当前位置：首页 → 问答吧 → 求一条简单的语句！！

求一条简单的语句！！

时间：2011-12-12

来源：互联网

table1表(字段a1,字段a2,字段a3)
求一条语句，得到下面的结果集：
N SUM
---------------
N1 sum(a1)
N2 sum(a2)
N3 sum(a3)

//N1，N2，N3为任意标识

即得到上面三条数据，求解！

作者: lcmlhs_2005 发布时间: 2011-12-12

SQL code

select 'N1' as Col,sum(a1) from table1
union all
select 'N2' as Col,sum(a2) from table1
union all
select 'N3' as Col,sum(a3) from table1

作者: roy_88 发布时间: 2011-12-12

select 'N1',sum(a1) union all
select 'N2',sum(a2) union all
select 'N3',sum(a3)

作者: niss 发布时间: 2011-12-12

SQL code

select 'N1' as N,sum(a1) AS [SUM] from table1
union all
select 'N2' as N,sum(a2) from table1
union all
select 'N3' as N,sum(a3) from table1


--or

SELECT *
FROM (SELECT SUM(a1) AS N1,SUM(a2) AS N2,SUM(a3) AS N3) AS a
PIVOT 
([Sum] FOR N IN(N1,N2,N3)) AS b

作者: roy_88 发布时间: 2011-12-12

楼上的，哪个最好呀？

作者: lcmlhs_2005 发布时间: 2011-12-12

引用 4 楼 lcmlhs_2005 的回复:

楼上的，哪个最好呀？

用一楼的比较好.

作者: qianjin036a 发布时间: 2011-12-12

DB2里有这个pivot吗？

作者: lcmlhs_2005 发布时间: 2011-12-12

引用 3 楼 roy_88 的回复:
SQL code
select 'N1' as N,sum(a1) AS [SUM] from table1
union all
select 'N2' as N,sum(a2) from table1
union all
select 'N3' as N,sum(a3) from table1

--or

SELECT *
FROM (SELECT SUM(a1) AS……

第二种方法，table1表写在哪儿？？？？？？？？

作者: lcmlhs_2005 发布时间: 2011-12-12

SELECT *
FROM (SELECT SUM(a1) AS N1,SUM(a2) AS N2,SUM(a3) AS N3) AS a
PIVOT
([Sum] FOR N IN(N1,N2,N3)) AS b

这个是不是没有写全？？？

作者: lcmlhs_2005 发布时间: 2011-12-12

引用 4 楼 lcmlhs_2005 的回复:
楼上的，哪个最好呀？

CTRL+L查看查询计划。

作者: fredrickhu 发布时间: 2011-12-12