SQL 转置查询
1.普通查询：
select * from chengji
结果：
xm zs ls we
语文 54 78 89
数学 87 67 95
2.转置：
SELECT 'zhangsan' AS 项目, [语文], [数学]
FROM
(
SELECT xm,zs FROM chengji
) AS c1
PIVOT
(
sum(zs)
FOR xm IN ([语文], [数学] )
) AS PivotTable
union
SELECT 'lisi' AS 项目, [语文], [数学]
FROM
(
SELECT xm,ls FROM chengji
) AS c1
PIVOT
(
sum(ls)
FOR xm IN ([语文], [数学] )
) AS PivotTable
union
SELECT 'wanger' AS 项目, [语文], [数学]
FROM
(
SELECT xm,we FROM chengji
) AS c1
PIVOT
(
sum(we)
FOR xm IN ([语文], [数学] )
) AS PivotTable
结果：
项目 语文 数学
lisi 78 67
wanger 89 95
zhangsan 54 87
3.另一种方法：
select 项目,sum(语文) AS 语文,sum(数学) AS 数学 from (
SELECT 'zhangsan' AS 项目,IIF(xm='语文',zs,0) AS 语文,IIF(xm='数学',zs,0) AS 数学
FROM
(
SELECT xm,zs FROM chengji
) AS a
union
SELECT 'lisi' AS 项目,IIF(xm='语文',ls,0) AS 语文,IIF(xm='数学',ls,0) AS 数学
FROM
(
SELECT xm,ISNULL(ls,0) as ls FROM chengji
) AS b
union
SELECT 'wanger' AS 项目,IIF(xm='语文',we,0) AS 语文,IIF(xm='数学',we,0) AS 数学
FROM
(
SELECT xm,we FROM chengji
) AS c
union
SELECT '合计' AS 项目,IIF(xm='语文',hj,0) AS 语文,IIF(xm='数学',hj,0) AS 数学
FROM
(
SELECT xm,sum(zs)+sum(ls)+sum(we) as hj FROM chengji group by xm
) AS d
) as x
group by 项目
4.mysql 写法：
select 项目,sum(语文) AS 语文,sum(数学) AS 数学 from (
SELECT 'zhangsan' AS 项目,IF(xm='语文',zs,0) AS 语文,IF(xm='数学',zs,0) AS 数学
FROM
(
SELECT xm,ifnull(zs,0) as zs FROM mymap.chengji
) AS a
union
SELECT 'lisi' AS 项目,IF(xm='语文',ls,0) AS 语文,IF(xm='数学',ls,0) AS 数学
FROM
(
SELECT xm,ls FROM mymap.chengji
) AS b
union
SELECT 'wanger' AS 项目,IF(xm='语文',we,0) AS 语文,IF(xm='数学',we,0) AS 数学
FROM
(
SELECT xm,we FROM mymap.chengji
) AS c
union
SELECT '合计' AS 项目,IF(xm='语文',hj,0) AS 语文,IF(xm='数学',hj,0) AS 数学
FROM
(
SELECT xm,sum(zs)+sum(ls)+sum(we) as hj FROM mymap.chengji group by xm
) AS d
) as x
group by 项目
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！