请教一个查询SQL语句

Faramitav 2015-10-16 12:37:12
有一个表A,字段如下
id s1 s2 tp
1 10 15 1
2 54 23 1
3 23 45 2

现在想按照tp来分类汇总S1和S2,而且要在同一行
例如查询后结果应该是这样的
相当于以tp为条件,分别sum(s1),sum(s2),然后显示到同一行上
ts1 ts2 ys1 ys2
64 38 23 25
...全文
109 4 点赞 打赏 收藏 举报
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Faramitav 2015-10-18
非常感谢~~
  • 打赏
  • 举报
回复
道玄希言 2015-10-16
静态行转列:

;with tbl1(id,s1,s2,tp) as
(
  select 1,10,15,1 union all
  select 2,54,23,1 union all
  select 3,23,45,2
 ),
 tbl2 as
 (
   select tp, sum(s1) as s1,sum(s2) as s2 
   from tbl1
   group by tp
),
tbl3 as
(
  select 
    ROW_NUMBER() OVER(order by tp) as nid, 
    s1, s2, 1 as id 
  from tbl2
)

select 
  t1.[1] as ts1, t2.[1] as ts2,  
  t1.[2] as ys1, t2.[2] as ys1
from 
(
select id, [1],[2]
from (select id, nid, s1 from tbl3) as t1
pivot
  (
     avg(s1) for nid in([1],[2])
  ) as T
) as t1
inner join 
(
select id, [1],[2]
from (select id, nid, s2 from tbl3) as t1
pivot
  (
     avg(s2) for nid in([1],[2])
  ) as T
)as t2
on t1.id = t2.id

ts1	ts2	ys1	ys1
64	38	23	45
  • 打赏
  • 举报
回复
道玄希言 2015-10-16
只有两行变成一行,可以这么写。

;with tbl1(id,s1,s2,tp) as
(
  select 1,10,15,1 union all
  select 2,54,23,1 union all
  select 3,23,45,2
 ),
 tbl2 as
 (
   select tp, sum(s1) as s1,sum(s2) as s2 
   from tbl1
   group by tp
),
tbl3 as
(
  select 
    ROW_NUMBER() OVER(order by tp) as nid, 
    s1, s2 
  from tbl2
)
select top 1 
  t1.s1 as ts1, t1.s2 as ts2, 
  t2.s1 as ys1, t2.s2 as ys2  
from tbl3 as t1 
left join tbl3 as t2
on t1.nid = t2.nid -1
  • 打赏
  • 举报
回复
山那頭 2015-10-16
sql列转行,网上帖子多,百度看看;
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-10-16 12:37
社区公告
暂无公告