--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] go create table [TB]([name] varchar(5),[pay] int,[pay_yes_no] varchar(6)) insert [TB] select 'huang',2000,'结算' union all select 'yang',5000,'未结算' union all select 'li',1200,'未结算' union all select 'huang',3000,'未结算' union all select 'huang',4000,'结算'select [name], pay=SUM([pay]), pay_yes_no=SUM(case when [pay_yes_no]='结算' then [pay] else 0 end) from [TB] group by [name]/* name pay pay_yes_no ----- ----------- ----------- huang 9000 6000 li 1200 0 yang 5000 0(3 行受影响) */ drop table [TB]
这个简单嘛。 case pay_yes_no when 顶 2楼
WITH temp AS( SELECT [name],SUM(pay) pay , CASE when pay_yes_no='结算' then sum(pay) else 0 end pay_yes_no FROM testsql t GROUP BY [name],pay_yes_no ) SELECT [name],SUM(pay) pay,sum(pay_yes_no) pay_yes_no FROM temp GROUP BY [name]
还是case when 比较简洁,也可以用子查询:select name,sum(pay) as pay , isnull((select sum(isnull(pay,0)) from tablename where name=a.name and pay_yes_no='结算') ,0) as pay_yes_no from tablename a group by name /* name pay pay_yes_no ----- ----------- ----------- huang 9000 6000 li 1200 0 yang 5000 0 */
create table [#TB]([name] varchar(5),[pay] int,[pay_yes_no] varchar(6)) insert [#TB] select 'huang',2000,'结算' union all select 'yang',5000,'未结算' union all select 'li',1200,'未结算' union all select 'huang',3000,'未结算' union all select 'huang',4000,'结算' select * from #TB select Name,sum(pay)pay ,isnull((select sum(b.pay) from #TB b where b.Name=a.Name and b.pay_yes_no='结算'),0) pay_yes_no from #TB a group by [Name] 调试过了,没问题的
select name,sum(pay) [pay], sum(case when pay_yes_no='结算' then pay else 0 end) [已结算] from tb group by [name]2楼正解 可以结贴了
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([name] varchar(5),[pay] int,[pay_yes_no] varchar(6))
insert [TB]
select 'huang',2000,'结算' union all
select 'yang',5000,'未结算' union all
select 'li',1200,'未结算' union all
select 'huang',3000,'未结算' union all
select 'huang',4000,'结算'select
[name],
pay=SUM([pay]),
pay_yes_no=SUM(case when [pay_yes_no]='结算' then [pay] else 0 end)
from [TB]
group by [name]/*
name pay pay_yes_no
----- ----------- -----------
huang 9000 6000
li 1200 0
yang 5000 0(3 行受影响)
*/
drop table [TB]
case pay_yes_no when
顶 2楼
SELECT [name],SUM(pay) pay ,
CASE when pay_yes_no='结算' then sum(pay) else 0 end pay_yes_no
FROM testsql t GROUP BY [name],pay_yes_no
)
SELECT [name],SUM(pay) pay,sum(pay_yes_no) pay_yes_no FROM temp GROUP BY [name]
isnull((select sum(isnull(pay,0))
from tablename where name=a.name and pay_yes_no='结算') ,0) as pay_yes_no
from tablename a group by name
/*
name pay pay_yes_no
----- ----------- -----------
huang 9000 6000
li 1200 0
yang 5000 0
*/
insert [#TB]
select 'huang',2000,'结算' union all
select 'yang',5000,'未结算' union all
select 'li',1200,'未结算' union all
select 'huang',3000,'未结算' union all
select 'huang',4000,'结算'
select * from #TB select Name,sum(pay)pay ,isnull((select sum(b.pay) from #TB b where b.Name=a.Name and b.pay_yes_no='结算'),0) pay_yes_no from #TB a group by [Name]
调试过了,没问题的
select name,sum(pay) [pay],
sum(case when pay_yes_no='结算' then pay else 0 end) [已结算]
from tb
group by [name]2楼正解 可以结贴了