设为首页 收藏本站
查看: 426|回复: 0

[经验分享] Mysql中包含日期、字符串类型的转置

[复制链接]

尚未签到

发表于 2016-10-20 10:11:19 | 显示全部楼层 |阅读模式
一般的行列转换都只针对数值型(int或double类型),这种情况下可以利用聚集函数(如SUM)来实现转置,网上也有比较完善的解决方案,可参考http://blog.iyunv.com/u3/90603/showart_2017912.html
但是如果要对字符串类型或日期等其他类型进行转置,情况则复杂很多了,这里进行了一些尝试,不需要借助存储过程或函数,也实现了这个转置。
源表:
ROWPROPERTYVALUE
1nameAndy
1addressBeijing
1remarktest
2nameBill
2addressShanghai

目标表:
ROWnameaddressremark
1AndyBeijingtest
2BillShanghaiNULL

这是个简单的例子,下面的情况还要稍微复杂一些:
源表(table1):
rowcodesidf
9000714idcard88888888NULLNULLNULL
9000714appdateNULLNULL2009-01-02 00:00:00NULL
9000714ageNULL11NULLNULL
9000714nameAndyNULLNULLNULL
9000719nameBillNULLNULLNULL
9000719ageNULL22NULLNULL
9000719idcard66666666NULLNULLNULL
9000719appdateNULLNULL2008-11-22 00:00:00NULL

目标表:
rownameageidcardappdate
9000714Andy11888888882009-01-02 00:00:00
9000719Bill22666666662008-11-22 00:00:00

第一步:先得到下面这个初步转置的中间表
rowidcardnameageappdate
9000714NULLNULLNULL2009-01-0200:00:00
9000714NULLNULL11NULL
900071488888888NULLNULLNULL
9000714NULLAndyNULLNULL
9000719NULLNULLNULL2008-11-2200:00:00
9000719NULLNULL22NULL
900071966666666NULLNULLNULL
9000719NULLBillNULLNULL


SELECT row,
CASE WHEN code = 'idcard' THEN s END AS 'idcard',
CASE WHEN code = 'name' THEN s END AS 'name',
CASE WHEN code = 'age' THEN i END AS 'age',
CASE WHEN code = 'appdate' THEN d END AS 'appdate'
FROM table1
GROUP BY row, code, s, d, i, f


第二部:进行分组聚合,可以得到目标表,sql如下:

SELECT row,
cast(group_concat(CASE WHEN cast(age as char) IS NOT NULL THEN age ELSE NULL END ) as signed) AS 'age',
group_concat(CASE WHEN name IS NOT NULL THEN name END ) AS 'name',
group_concat(CASE WHEN idcard IS NOT NULL THEN idcard END ) AS 'idcard',
cast(group_concat(CASE WHEN cast(appdate as char) IS NOT NULL THEN appdate ELSE NULL END ) as datetime) AS 'appdate'
FROM (
SELECT row,
CASE WHEN code = 'idcard' THEN s END AS 'idcard',
CASE WHEN code = 'name' THEN s END AS 'name',
CASE WHEN code = 'age' THEN i END AS 'age',
CASE WHEN code = 'appdate' THEN d END AS 'appdate'
FROM table1
GROUP BY row, code, s, d, i, f
)t
GROUP BY row


对于int型的age字段,可以用sum即可,但是采用下面的写法是为了统一转换的过程(所有类型都通过group_concat来处理),即把int型先转换成char型,用group_concat拼接完成后再转换成int型(datetime型也是这样处理的),这个处理只适用于同一个row只有一个age值:
cast(group_concat(CASE WHEN cast(age as char) IS NOT NULL THEN age ELSE NULL END ) as signed) AS 'age'

也可以换成下面的sql:

SUM(CASE WHEN age IS NOT NULL THEN age ELSE 0 END ) AS 'age'

(appdate字段解释类似)
另外,由于SQL Server和Oracle中没有类似于group_concat的函数,实现起来好像要麻烦很多。
附:相关sql

CREATE TABLE `table1` (
`row` int(20) default NULL,
`code` varchar(255) default NULL,
`s` varchar(8000) default NULL,
`d` datetime default NULL,
`i` int(20) default NULL,
`f` float default NULL
);
INSERT INTO table1 VALUES (9000714, 'idcard', '88888888', NULL, NULL, NULL );
INSERT INTO table1 VALUES (9000714, 'appdate', NULL, '2009-01-02 00:00:00', NULL, NULL );
INSERT INTO table1 VALUES (9000714, 'age', NULL, NULL, 11, NULL);
INSERT INTO table1 VALUES (9000714, 'name',  'Andy', NULL, NULL, NULL );
INSERT INTO table1 VALUES (9000719, 'name', 'Bill', NULL, NULL, NULL );
INSERT INTO table1 VALUES (9000719, 'age', NULL, NULL, 22, NULL );
INSERT INTO table1 VALUES (9000719, 'idcard', '66666666', NULL, NULL, NULL );
INSERT INTO table1 VALUES (9000719, 'appdate', NULL, '2008-11-22 00:00:00', NULL, NULL );

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-288810-1-1.html 上篇帖子: mysql 一些常用命令 下篇帖子: MySQL随机取出10条数据
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表