Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1844352
  • 博文数量: 148
  • 博客积分: 7697
  • 博客等级: 少将
  • 技术积分: 3071
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-10 23:04
个人简介

MiBDP,数据开发、项目团队、数据应用和产品在路上,金融保险、互联网网游、电商、新零售行业、大数据和AI在路上。对数仓、模型、ETL、数据产品应用了解。DTCC 2013演讲嘉宾,曾做过两款大获好评的数据产品平台。知识星球ID:35863277

文章分类
文章存档

2020年(1)

2019年(2)

2017年(2)

2016年(5)

2015年(1)

2014年(1)

2013年(6)

2012年(5)

2011年(24)

2010年(28)

2009年(1)

2008年(6)

2007年(30)

2006年(36)

分类: Mysql/postgreSQL

2013-11-14 21:21:48

用过Mysql的都知道她有一个很好的实现行转列功能的函数group_concat函数,非常方便

点击(此处)折叠或打开

  1. SELECT
  2.     *
  3. FROM
  4.     group_test;
  5.  
  6. SELECT
  7.     id,
  8.     GROUP_CONCAT(sub_id)
  9. FROM
  10.     `group_test`
  11. GROUP BY
  12.     id;






现在的需求是有上面图二类似的结果集,需要把列二拆分 转换成行记录


我们知道如果是单条记录通过SUBSTRING_INDEX容易实现


点击(此处)折叠或打开

  1. select id,SUBSTRING_INDEX(sub_id,',',1) from group_test where id=3
  2. UNION
  3. select id,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_id,',',2),',',-1) from group_test where id=3
  4. UNION
  5. select id,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_id,',',3),',',-1) from group_test where id=3

但是如果是N条呢?同样也是可以使用SUBSTRING_INDEX来实现,只不过需要一个配置表,通过CROSS JOIN交叉连接实现,先看下CROSS JOIN

点击(此处)折叠或打开

  1. SELECT
  2.     *
  3. FROM
  4.     (SELECT 1 UNION SELECT 2) t1
  5. CROSS JOIN (SELECT 3 UNION SELECT 4) t2


下面就通过CROSS JOIN和SUBSTRING_INDEX实现我们的需求,首先构建一个配置表


点击(此处)折叠或打开

  1. CREATE TABLE digits (digit INT(1));
  2. INSERT INTO digits
  3. VALUES
  4.     (0),
  5.     (1),
  6.     (2),
  7.     (3),
  8.     (4),
  9.     (5),
  10.     (6),
  11.     (7),
  12.     (8),
  13.     (9);
  14. CREATE TABLE sequence (seq INT(3));
  15. INSERT INTO sequence (
  16.     SELECT
  17.         D1.digit + D2.digit * 10
  18.     FROM
  19.         digits D1
  20.     CROSS JOIN digits D2
  21. );
然后


点击(此处)折叠或打开

  1. SELECT
  2.     id,
  3.     SUBSTRING_INDEX(
  4.         SUBSTRING_INDEX(sub_id, ',', seq),
  5.         ',' ,- 1
  6.     ) sub_id,
  7.     seq
  8. FROM
  9.     sequence
  10. CROSS JOIN group_test
  11. WHERE
  12.     seq BETWEEN 1
  13. AND (
  14.     SELECT
  15.         1 + LENGTH(sub_id) - LENGTH(REPLACE(sub_id, ',', ''))
  16. )
  17. ORDER BY
  18.     id,
  19.     sub_id;



然后就没有然后了。如图上的代码 有几个地方使用还是很巧妙的 不是吗?
最后此方法是不是比写个存储过程或者PHP/PYTHON简单些呢^_^
阅读(39933) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~