UC知道怎么用一条SQL语句把一个这样的表
来源：百度知道 编辑：UC知道 时间：2024/06/04 12:18:41
address fruit money
北京 苹果 5
上海 苹果 6
北京 梨子 7
上海 梨子 5
广州 香蕉 4
查询显示成
address 苹果 梨子 香蕉
北京 5 7 null
上海 6 5 null
广州 null null 4
水果种类是不固定的
select address,MAX(case when fruit='苹果' then money else null end) as 苹果,MAX(case when fruit='梨子' then money else null end) as 梨子,MAX(case when fruit='香蕉' then money else null end) as 香蕉 from tablename group by address
select address,
SUM(case when fruit='苹果' then money else null end) 苹果,
SUM(case when fruit='梨子' then money else null end) 梨子,
SUM(case when fruit='香蕉' then money else null end) 香蕉
from aa
group by address
order by 1
如果fruit这列的种类不止上述3种，需要用对sql做动态拼接，这里不做赘述了
用静态SQL
方法1.
select address,
max(case fruit when '苹果' then money else null end) as 苹果,
max(case fruit when '梨子' then money else null end) as 梨子,
max(case fruit when '香蕉' then money else null end) as 香蕉
from aa
group by address
方法2:
select address,
苹果=max(case fruit when '苹果' then money else null end),
梨子=max(case