我的表结构是这样的
表Aid amount1 type
1 10 1
1 8 1
2 5 2
1 9 2我要查出这样的结果
根据type相同的并且id相同的amount1 和amount2求和
得出这样的结果
id type=1的amount1求和 type=2的mount1求和
1 18 9
2 0 5请问怎么做啊谢谢
表Aid amount1 type
1 10 1
1 8 1
2 5 2
1 9 2我要查出这样的结果
根据type相同的并且id相同的amount1 和amount2求和
得出这样的结果
id type=1的amount1求和 type=2的mount1求和
1 18 9
2 0 5请问怎么做啊谢谢
sum(case type when 1 then amount1 end) as [type=1的amount1求和],
sum(case type when 2 then amount1 end) as [type=2的amount1求和]
from tablename
group by id
id,
[type=1的amount1求和] = SUM(Case type When 1 Then amount1 Else 0 End),
[type=2的amount1求和] = SUM(Case type When 2 Then amount1 Else 0 End)
From
A
Group By
id
抢分啊..
create table ttt
(
id int,
amount1 int,
type int
)insert into ttt select 1, 10, 1
insert into ttt select 1, 8 , 1
insert into ttt select 2, 5, 2
insert into ttt select 1, 9, 2--查询
select id,
sum(case type when 1 then amount1 end) as [type=1的amount1求和],
sum(case type when 2 then amount1 end) as [type=2的amount1求和]
from ttt
group by id--结果
id type=1的amount1求和 type=2的amount1求和
----------- ---------------- ----------------
1 18 9
2 NULL 5(所影响的行数为 2 行)
我给你的短息你看啦没啊~
create table #A(id int, amount1 int, type int)
insert #A select 1,10,1
union all select 1,8,1
union all select 2,5,2
union all select 1,9,2declare @s varchar(8000)
select @s='select id'
select @s = @s + ',sum( case type when ' + convert(varchar,type) + ' then amount1 else 0 end) [type=' +convert(varchar,type) + '的amount1求和] ' from (select distinct type from #A) a
select @s = @s + 'from #A group by id'
exec (@s)drop table #A
/*
id type=1的amount1求和 type=2的amount1求和
----------- ---------------- ----------------
1 18 9
2 0 5
*/
go
create table tem
(
id int,
amount1 int,
type int
)
go
insert into tem select 1, 10, 1
insert into tem select 1, 8, 1
insert into tem select 2, 5, 2
insert into tem select 1, 9, 2
goselect id,
isnull(sum(case type when 1 then amount1 end),0) as [type=1的amount1求和],
isnull(sum(case type when 2 then amount1 end),0) as [type=2的amount1求和]
from tem
group by id
go
sum(case when [type]=1 then amount1 else 0 end) as [type=1的amount1求和],
sum(case when [type]=2 then amount1 else 0 end) as [type=2的amount1求和]
from A
group by [id]
order by [id]