收藏 不显示删除回复显示所有回复显示星级回复显示得分回复 计算收出与支出的问题[问题点数:20分，结帖人:GDTOPONE]

GROUP BY H_DAY

select 日期,
收入 = max(case when H_NAME = '收入' then H_CON else 0 end),
支出 = max(case when H_NAME = '支出' then H_CON else 0 end)
from H_COUNT 
group by 日期

select 日期=H_DAY ,
收入 = max(case when H_NAME = '收入' then H_CON else 0 end),
支出 = max(case when H_NAME = '支出' then H_CON else 0 end)
from H_COUNT 
group by H_DAY 

select H_DAY AS 日期,
SUM(case when H_NAME = '收入' then H_CON else 0 end) AS 收入,
SUM(case when H_NAME = '支出' then H_CON else 0 end) AS 支出
from H_COUNT 
group by H_DAY

select H_DAY AS 日期,
SUM(case when H_NAME = '收入' then H_CON else NULL end) AS 收入,
SUM(case when H_NAME = '支出' then H_CON else NULL end) AS 支出
from H_COUNT 
group by H_DAY

create table  H_COUNT (H_DAY datetime,               H_CON int,      H_NAME nvarchar(5))
insert H_COUNT select '2007-1-10',           10,             '收入' 
insert H_COUNT select '2007-1-11',           20,             '收入' 
insert H_COUNT select '2007-1-10',             5,             '支出' 
insert H_COUNT select '2007-1-11',             3,             '支出'

--datetime类型时，只保存日期
go

select 
	H_DAY=convert(varchar,H_DAY,120),
	sum(case when H_NAME='收入' then H_CON else 0 end) as 收入,--这里要用sum
	sum(case when H_NAME='支出' then H_CON else 0 end) as 支出--这里要用sum
from 
	H_COUNT
group by convert(varchar,H_DAY,120)

-----动态方法
declare @s nvarchar(1000)
set @s='select 	H_DAY=convert(varchar,H_DAY,120)'
select @s=@s+','+quotename(H_NAME)+'=sum(case when H_NAME='+quotename(H_NAME,'''')+' then H_CON else 0 end)'
from 
	H_COUNT 
group by H_NAME

exec (@s+' from 	H_COUNT group by convert(varchar,H_DAY,120)')

/*
H_DAY                          收入          支出          
------------------------------ ----------- ----------- 
2007-01-10 00:00:00            10          5
2007-01-11 00:00:00            20          3

（所影响的行数为 2 行）

*/

--drop table H_COUNT