[Quote=引用 2 楼 jiangshun 的回复:]
--> 测试数据:[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 'ya……
[/Quote]顶2楼
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]
调试过了,没问题的
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]
[Quote=引用 2 楼 jiangshun 的回复:]
--> 测试数据:[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 'ya……
[/Quote]
正解 顶
[Quote=引用 2 楼 jiangshun 的回复:]
--> 测试数据:[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 'ya……
[/Quote]
mark ...
[Quote=引用 2 楼 jiangshun 的回复:]
--> 测试数据:[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 'ya……
[/Quote]
UP +1
--> 测试数据:[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