求SQL语句,按年分类并显示月份详细累计数据

Question

有数据库TB,字段如下:
ID[自增ID]  KHID[客户ID号]  UserID[用户ID号]  STime[开始时间]  ETime[结束时间]  Money[产生费用]ID  KHID  UserID    STime       ETime      Money
1    1      1     2009-3-1   2009-3-31     100.0000
2    1      1     2009-4-1   2009-4-30     100.0000
3    1      1     2009-5-1   2009-8-31     200.0000
4    1      1     2009-9-1   2009-9-30     100.0000
5    1      1     2009-10-1  2009-12-31    500.0000
6    1      1     2010-1-1   2010-3-31     200.0000
7    1      1     2010-4-1   2010-9-30     600.0000
8    1      1     2010-10-1  2010-12-31    200.0000
9    1      1     2011-1-1   2011-3-31     100.0000
10   1      1     2011-4-1   2011-10-5     800.0000
11   1      1     2011-10-6  2011-11-31    400.0000如果输入客户ID号为1,用户ID号为1,查询日期为2010-10-31,希望得到的结果是:日期               金额        累计金额
2009年                            0
3月1日-31日       100.00        100.00
4月1日-30日       100.00        200.00
5月1日-8月31日    200.00        400.00     
9月1日-30日       100.00        500.00
10月1日-12月31日  500.00        1000.00    
2010年                          1000.00
1月1日-3月31日    200.00        1200.00
4月1日-9月30日    600.00        1800.00如果查询日期是2010-12-31，那么结果就应该是：日期               金额        累计金额
2009年                            0
3月1日-31日       100.00        100.00
4月1日-30日       100.00        200.00
5月1日-8月31日    200.00        400.00     
9月1日-30日       100.00        500.00
10月1日-12月31日  500.00        1000.00    
2010年                          1000.00
1月1日-3月31日    200.00        1200.00
4月1日-9月30日    600.00        1800.00
10月1日-12月31日  200.00        2000.00再如查询日期是2011-10-6，那么结果就是：日期               金额        累计金额
2009年                            0
3月1日-31日       100.00        100.00
4月1日-30日       100.00        200.00
5月1日-8月31日    200.00        400.00     
9月1日-30日       100.00        500.00
10月1日-12月31日  500.00        1000.00    
2010年                          1000.00
1月1日-3月31日    200.00        1200.00
4月1日-9月30日    600.00        1800.00
10月1日-12月31日  200.00        2000.00
2011年                          2000.00
1月1日-3月31日    100.00        2100.00
4月1日-10月5日    800.00        2900.00显然最后一条记录是不显示的，因为查询日期与每个记录的结束日期做比较，求这个SQL语句

Answer 1

--> --> (Roy)生成測試數據if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[KHID] int,[UserID] int,[STime] Datetime,[ETime] datetime,[Money] decimal(18,4))
Insert #T
select 1,1,1,'2009-3-1',N'2009-3-31',100.0000 union all
select 2,1,1,'2009-4-1',N'2009-4-30',100.0000 union all
select 3,1,1,'2009-5-1',N'2009-8-31',200.0000 union all
select 4,1,1,'2009-9-1',N'2009-9-30',100.0000 union all
select 5,1,1,'2009-10-1',N'2009-12-31',500.0000 union all
select 6,1,1,'2010-1-1',N'2010-3-31',200.0000 union all
select 7,1,1,'2010-4-1',N'2010-9-30',600.0000 union all
select 8,1,1,'2010-10-1',N'2010-12-31',200.0000 union all
select 9,1,1,'2011-1-1',N'2011-3-31',100.0000 union all
select 10,1,1,'2011-4-1',N'2011-10-5',800.0000 union all
select 11,1,1,'2011-10-6',N'2011-11-30',400.0000
Go
declare @dt datetime
set @dt='2010-10-31'
select [日期]=case when [日期]='' then RTRIM([年份])+'年' else [日期] end,
金额,[累计]
from 
(Select
[年份]=YEAR([STime]),
[日期]=rtrim(DATEPART(m,[STime]))+'月'+rtrim(DATEPART(d,[STime]))+'日'+
case when DATEDIFF(m,[STime],[ETime])>0 then '-'+rtrim(DATEPART(m,[ETime]))+'月' else '-' end+
rtrim(DATEPART(d,[ETime]))+'日',
rtrim([Money]) as  金额 ,
[累计],
MONTH([STime]) as ord
from (select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime])from #T as a where [ETime]<=@dt) as a
union all
select 
[年份]=YEAR([STime])+1,
[日期]='',
[金额]='',
[累计],
0 as ord
from 
(select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime])from #T as a where [STime]<@dt) as a 
where month([ETime])=12 and YEAR([ETime])<YEAR(@dt)
union all
select [年份]=year(MIN([STime])),[日期]='',[金额]='',[累计]=0,ord=0 from #T where [STime]<@dt
)t
order by t.[年份],t.ord/*
日期 金额 累计
2009年 0.0000
3月1日-31日 100.0000 100.0000
4月1日-30日 100.0000 200.0000
5月1日-8月31日 200.0000 400.0000
9月1日-30日 100.0000 500.0000
10月1日-12月31日 500.0000 1000.0000
2010年 1000.0000
1月1日-3月31日 200.0000 1200.0000
4月1日-9月30日 600.0000 1800.0000
*/

Answer 2

结果显示中不需要有表头,是不是语句要少一层SELECT?
结果是这样就可以了2009年                       0.0000
3月1日-31日    100.0000    100.0000
4月1日-30日    100.0000    200.0000
5月1日-8月31日 200.0000    400.0000
9月1日-30日    100.0000    500.0000
10月1日-12月31日 500.0000  1000.0000
2010年                       1000.0000
1月1日-3月31日  200.0000  1200.0000
4月1日-9月30日  600.0000  1800.0000

Answer 3

表头是不影响的，如果程序调用需要有列名显示,看看是不是这样结果
declare @dt datetime
set @dt='2010-10-31'
select 
case when [日期]='' then RTRIM([年份])+'年' else [日期] end,
cast(金额 as nvarchar(20)),cast([累计] as  nvarchar(20))
from 
(Select
[年份]=YEAR([STime]),
[日期]=rtrim(DATEPART(m,[STime]))+'月'+rtrim(DATEPART(d,[STime]))+'日'+
case when DATEDIFF(m,[STime],[ETime])>0 then '-'+rtrim(DATEPART(m,[ETime]))+'月' else '-' end+
rtrim(DATEPART(d,[ETime]))+'日',
rtrim([Money]) as  金额 ,
[累计],
MONTH([STime]) as ord
from (select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime])from #T as a where [ETime]<=@dt) as a
union all
select 
[年份]=YEAR([STime])+1,
[日期]='',
[金额]='',
[累计],
0 as ord
from 
(select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime])from #T as a where [STime]<@dt) as a 
where month([ETime])=12 and YEAR([ETime])<YEAR(@dt)
union all
select [年份]=year(MIN([STime])),[日期]='',[金额]='',[累计]=0,ord=0 from #T where [STime]<@dt
)t
order by t.[年份],t.ord/*
2009年 0.0000
3月1日-31日 100.0000 100.0000
4月1日-30日 100.0000 200.0000
5月1日-8月31日 200.0000 400.0000
9月1日-30日 100.0000 500.0000
10月1日-12月31日 500.0000 1000.0000
2010年 1000.0000
1月1日-3月31日 200.0000 1200.0000
4月1日-9月30日 600.0000 1800.0000
/

Answer 4

declare @dt datetime
set @dt='2010-10-31'
select 
cast(KHID as nvarchar(50))
,cast(UserID as nvarchar(50)),
case when [日期]='' then RTRIM([年份])+'年' else [日期] end,
cast(金额 as nvarchar(20)),cast([累计] as  nvarchar(20))
from 
(Select
KHID=RTRIM(KHID),UserID=RTRIM(UserID),
[年份]=YEAR([STime]),
[日期]=rtrim(DATEPART(m,[STime]))+'月'+rtrim(DATEPART(d,[STime]))+'日'+
case when DATEDIFF(m,[STime],[ETime])>0 then '-'+rtrim(DATEPART(m,[ETime]))+'月' else '-' end+
rtrim(DATEPART(d,[ETime]))+'日',
rtrim([Money]) as  金额 ,
[累计],
MONTH([STime]) as ord
from (select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime])from #T as a where [ETime]<=@dt) as a
union all
select 
KHID='',UserID='',
[年份]=YEAR([STime])+1,
[日期]='',
[金额]='',
[累计],
0 as ord
from 
(select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime])from #T as a where [STime]<@dt) as a 
where month([ETime])=12 and YEAR([ETime])<YEAR(@dt)
union all
select KHID='',UserID='',[年份]=year(MIN([STime])),[日期]='',[金额]='',[累计]=0,ord=0 from #T where [STime]<@dt
)t
order by t.[年份],t.ord/*
2009年 0.0000
1 1 3月1日-31日 100.0000 100.0000
1 1 4月1日-30日 100.0000 200.0000
1 1 5月1日-8月31日 200.0000 400.0000
1 1 9月1日-30日 100.0000 500.0000
1 1 10月1日-12月31日 500.0000 1000.0000
2010年 1000.0000
1 1 1月1日-3月31日 200.0000 1200.0000
1 1 4月1日-9月30日 600.0000 1800.0000
*/
加上KHID,UserID，表头是解释列的数据，只有数据你怎么判断列的什么数据?

Answer 5

不好意思搞错了 是需要表头的
不是加上显示KFID,USERID这两个字段,是需要筛选,因为数据库还有USERID为其他的数据
应该哪个where语句上加上" where KFID=@KFID and UserID=@UserID"
只是不清楚 应该加在哪里

Answer 6

暈 KFID=@KFID 是客戶 KHID?自己提問能細心點麼
--> --> (Roy)生成測試數據if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[KHID] int,[UserID] int,[STime] Datetime,[ETime] datetime,[Money] decimal(18,4))
Insert #T
select 1,1,1,'2009-3-1',N'2009-3-31',100.0000 union all
select 2,1,1,'2009-4-1',N'2009-4-30',100.0000 union all
select 3,1,1,'2009-5-1',N'2009-8-31',200.0000 union all
select 4,1,1,'2009-9-1',N'2009-9-30',100.0000 union all
select 5,1,1,'2009-10-1',N'2009-12-31',500.0000 union all
select 6,1,1,'2010-1-1',N'2010-3-31',200.0000 union all
select 7,1,1,'2010-4-1',N'2010-9-30',600.0000 union all
select 8,1,1,'2010-10-1',N'2010-12-31',200.0000 union all
select 9,1,1,'2011-1-1',N'2011-3-31',100.0000 union all
select 10,1,1,'2011-4-1',N'2011-10-5',800.0000 union all
select 11,1,1,'2011-10-6',N'2011-11-30',400.0000
Go
declare @dt datetime
set @dt='2010-10-31'DECLARE @KHID INT,@UserID INTSELECT @KHID=1,@UserID=1select [日期]=case when [日期]='' then RTRIM([年份])+'年' else [日期] end,
金额,[累计]
from 
(Select
[年份]=YEAR([STime]),
[日期]=rtrim(DATEPART(m,[STime]))+'月'+rtrim(DATEPART(d,[STime]))+'日'+
case when DATEDIFF(m,[STime],[ETime])>0 then '-'+rtrim(DATEPART(m,[ETime]))+'月' else '-' end+
rtrim(DATEPART(d,[ETime]))+'日',
rtrim([Money]) as  金额 ,
[累计],
MONTH([STime]) as ord
from (select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime] AND [KHID]=a.KHID AND UserID=a.UserID)from #T as a where [ETime]<=@dt AND KHID=@KHID and UserID=@UserID) as a
union all
select 
[年份]=YEAR([STime])+1,
[日期]='',
[金额]='',
[累计],
0 as ord
from 
(select * ,[累计]=(select SUM([Money]) from #T where [STime]<=a.[STime] AND KHID=a.KHID AND UserID=a.UserID)from #T as a where [ETime]<=@dt AND KHID=@KHID and UserID=@UserID) as a 
where month([ETime])=12 and YEAR([ETime])<YEAR(@dt) AND KHID=@KHID and UserID=@UserID
union all
select [年份]=year(MIN([STime])),[日期]='',[金额]='',[累计]=0,ord=0 from #T where [STime]<@dt AND KHID=@KHID and UserID=@UserID
)t
order by t.[年份],t.ord
GO
/*
日期 金额 累计
2009年 0.0000
3月1日-31日 100.0000 100.0000
4月1日-30日 100.0000 200.0000
5月1日-8月31日 200.0000 400.0000
9月1日-30日 100.0000 500.0000
10月1日-12月31日 500.0000 1000.0000
2010年 1000.0000
1月1日-3月31日 200.0000 1200.0000
4月1日-9月30日 600.0000 1800.0000
*/