postgresql行转列
- 作者: 卓而不群
- 来源: 51数据库
- 2020-10-01
select mname as 姓名,
sum(case fname when '语文' then score else 0 end ) 语文,
sum(case fname when '数学' then score else 0 end ) 数学,
sum(case fname when '英语' then score else 0 end ) 英语,
sum(case fname when '历史' then score else 0 end ) 历史
from member a ,F b,score c where a.mid=c.mid and b.fid=c.fid group by mname
你可先建立一个自定义函数，脚本如下： --获取城市名称 create function getcityname returns varchar(1000) begin declare @sql varchar(1000) select @sql=isnull(@sql+',','')+ city_name from dbo.htc_info_areacity b,htc_info_areaprovince a where a.province_code=b.province_code set @sql=replace(convert(varchar(1000),@sql),'请选择,','') return @sql end 然后在查询语句中如下调用即可： select a.privince_code ,dbo.getcityname() as cityname from htc_info_areaprovince a
sum(case fname when '语文' then score else 0 end ) 语文,
sum(case fname when '数学' then score else 0 end ) 数学,
sum(case fname when '英语' then score else 0 end ) 英语,
sum(case fname when '历史' then score else 0 end ) 历史
from member a ,F b,score c where a.mid=c.mid and b.fid=c.fid group by mname
你可先建立一个自定义函数，脚本如下： --获取城市名称 create function getcityname returns varchar(1000) begin declare @sql varchar(1000) select @sql=isnull(@sql+',','')+ city_name from dbo.htc_info_areacity b,htc_info_areaprovince a where a.province_code=b.province_code set @sql=replace(convert(varchar(1000),@sql),'请选择,','') return @sql end 然后在查询语句中如下调用即可： select a.privince_code ,dbo.getcityname() as cityname from htc_info_areaprovince a
推荐阅读
