首页 > 数据库技术 > 详细

SQL分组排名+行转列

时间:2016-02-18 17:51:12      阅读:215      评论:0      收藏:0      [点我收藏+]

SELECT
max(CASE TT.groupname WHEN ‘A‘ THEN TT.name end) AS ‘nameA‘,
max(CASE TT.groupname WHEN ‘A‘ THEN TT.age end) AS ‘ageA‘,
max(CASE TT.groupname WHEN ‘B‘ THEN TT.name end) AS ‘nameB‘,
max(CASE TT.groupname WHEN ‘B‘ THEN TT.age end) AS ‘ageB‘,
max(CASE TT.groupname WHEN ‘C‘ THEN TT.name end) AS ‘nameC‘,
max(CASE TT.groupname WHEN ‘C‘ THEN TT.age end) AS ‘ageC‘,
max(CASE TT.groupname WHEN ‘D‘ THEN TT.name end) AS ‘nameD‘,
max(CASE TT.groupname WHEN ‘D‘ THEN TT.age end) AS ‘ageD‘
FROM
(
select T.groupname,T.name,T.age from (
select ‘A‘ as ‘groupname‘, ‘AA‘ as ‘name‘, 23 as ‘age‘
union all
select ‘B‘, ‘BB‘, 20
union all
select ‘B‘, ‘CC‘, 25
union all
select ‘C‘, ‘DD‘, 27
union all
select ‘C‘, ‘EE‘, 21
union all
select ‘C‘, ‘FF‘, 26
union all
select ‘D‘, ‘GG‘, 28
union all
select ‘D‘, ‘HH‘, 29) as T
where T.groupname in (‘A‘,‘B‘,‘C‘,‘D‘)
group by T.groupname
having MIN(T.age)=T.age) TT

SQL分组排名+行转列

原文:http://www.cnblogs.com/bluelight4587/p/5198775.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!