SQL分组排名+行转列

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