简单sql，跨年金额怎样分配到每年金额，用存储过程实现，求解 ！！！

Question

T1
房间号   单价    起期                止期                 金额      收费日期
01          0.70    2017-03-14   2018-03-13      997    2017-05-01
02          1.20    2017-04-11   2018-04-10     1438   2017-10-08
03          1.00    2017-05-11   2017-12-31      600    2017-07-10T2
房间号   类型
01          大床
02          双床
03          双床T3
房间号   单价    起期                止期                金额        类型   收费日期       年度1    金额1     年度2     金额2    
01          0.70    2017-03-14   2018-03-13     997      大床   2017-05-01    2017     800       2018       197
02          1.20    2017-04-11   2018-04-10     1438    双床   2017-10-08    2017    1044     2018       394
03          1.00    2017-05-11   2017-12-31     600      双床   2017-07-10    2017     600       2018          0计算起止期间金额分别在每年的金额，年度2的金额=金额 - 年度1的金额
平均每天单价 = 金额 / 起止期间的天数
年度1的金额 = 平均每天单价 * 在年度1的天数
计算单价（保留14位），金额1等要四合五入保留两位
可能会有第三年、第四年等等，求怎样写sql得到T3 (要sql2000的语句，用存储过程得到的)T3要求写成存储过程，要求有四个参数，一个参数是年度，一个类型，另外两个是收费开始日期，收费结束日期（根据给出时间段），来查询收费日期在这个时间段范围内的数据 

Answer 1

按照你的需求，我觉得年度是多余的，因为开始时间和结束时间本身就有年度信息
如果都传，是两个都要满足？而且增加年度参数，就以为只能算一年的，那么SQL就不用搞这么复杂了
我没写存储过程，你可以自己创建，将我这个例子里的参数变成存储过程的参数就行
另外我这里关联两次 master.dbo.spt_values 是因为一个最大数字不够用，你可以自己建个序列，可以SQL简化
declare @yr int,@roomtype nvarchar(10),@startDate datetime,@endDate datetime 
set @yr=2017
set @roomtype=N'大床'
set @startdate='2017-03-14'
set @enddate ='2017-12/31'
  
declare @cols nvarchar(4000),@sql nvarchar(4000)
  
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N' as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
       +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st
where sv.type='P'
  
set @sql=N'select t.房间号,t2.类型,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,datediff(d,起期,止期)+1 as 总天数,'+@cols+char(13)
        +N' from #t as t inner join #t2 as t2 on t.房间号=t2.房间号 inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P'' 
and (hsv.number*200+sv.number) between 0 and datediff(d,起期,止期) '+char(13)
        +N'where t2.类型=N'''+@roomtype+''' AND hsv.number<=200 and sv.number<200 AND '+N'datediff(d,dateadd(d,hsv.number*200+sv.number,起期),'''+convert(varchar,@startDate,111)+N''')<=0 AND datediff(d,dateadd(d,hsv.number*200+sv.number,起期),'''+convert(varchar,@endDate,111)+N''')>=0'+char(13)
        +N'group by t.房间号,t2.类型,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by t.房间号'
select @sql
  
exec(@sql)
+-----+----+-----+-------------------------+-------------------------+-----+----------------+-----+------+-----+------------------+------+-----+-----+
| 房间号 | 类型 | 单价  | 起期                      | 止期                      | 金额  | 计算单价           | 总天数 | 年度1  | 天数1 | 金额1              | 年度2  | 天数2 | 金额2 |
+-----+----+-----+-------------------------+-------------------------+-----+----------------+-----+------+-----+------------------+------+-----+-----+
| 01  | 大床 | 0.7 | 2017-03-14 00:00:00:000 | 2018-03-13 00:00:00:000 | 997 | 2.731506849315 | 365 | 2017 | 293 | 800.331506849295 | 2018 | 0   | 0   |
+-----+----+-----+-------------------------+-------------------------+-----+----------------+-----+------+-----+------------------+------+-----+-----+
如果不在指定范围的年度不想列出，则将生成COLS的语句改为下面，当然如果你确定有年度的参数，直接用年度就不用开始和结束时间算了select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N' as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
       +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t 
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st and sv.number+st between year(@startDate) and  year(@endDate)
where sv.type='P'
+-----+----+-----+-------------------------+-------------------------+-----+----------------+-----+------+-----+------------------+
| 房间号 | 类型 | 单价  | 起期                      | 止期                      | 金额  | 计算单价           | 总天数 | 年度1  | 天数1 | 金额1              |
+-----+----+-----+-------------------------+-------------------------+-----+----------------+-----+------+-----+------------------+
| 01  | 大床 | 0.7 | 2017-03-14 00:00:00:000 | 2018-03-13 00:00:00:000 | 997 | 2.731506849315 | 365 | 2017 | 293 | 800.331506849295 |
+-----+----+-----+-------------------------+-------------------------+-----+----------------+-----+------+-----+------------------+

Answer 2

@ch21st ，这个年度和开始和结束时间都要用的，用模糊查询方式就行，不要考虑它是否合理

Answer 3

ch21st ，如果T#，T#3T表的结构变下，后面的天数1，金额1等怎么都不对了，怎么改呢if object_id(N'Tempdb..#t') is not null drop table #T
if object_id(N'Tempdb..#t3') is not null drop table #T3
go
CREATE TABLE #t(房间号 VARCHAR(10),期间 VARCHAR(10),标准名称 VARCHAR(20),单价 DECIMAL(10, 2),起期 DATETIME,止期 DATETIME,金额 DECIMAL(10, 2))
CREATE TABLE #t3(标准名称 VARCHAR(20),收费项目 nvarchar(20))
GO
    
INSERT INTO #t
SELECT  '01' ,'1季度','标间(标准)',0.70 ,'2017-01-01' ,'2017-03-31' ,997  UNION ALL
SELECT  '01' ,'2季度','标间(标准)',1.20 ,'2017-04-01' ,'2017-06-30' ,1438 UNION ALL
SELECT  '02' ,'1季度','家庭间(标准)',1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT  '03' ,'1季度','家庭间(标准)',1.00 ,'2017-05-11' , '2017-12-31' , 600
go
INSERT INTO #t3
SELECT  '标间(标准)',N'标间' UNION ALL
SELECT  '标间(标准)',N'标间' UNION ALL
SELECT  '家庭间(标准)',N'家庭间'
   
   
declare @cols nvarchar(4000),@sql nvarchar(4000)
   
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N'as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
       +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st
where sv.type='P'
   
set @sql=N'select t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,datediff(d,起期,止期)+1 as 总天数,'+@cols+N' from #t as t inner join #t3 as t3 on t.标准名称=t3.标准名称 inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P'' 
and (hsv.number*200+sv.number) between 0  and datediff(d,起期,止期) where hsv.number<=200 and sv.number<200 group by t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by t.房间号'
select @sql
   
exec(@sql) 

Answer 4

结果变成二倍是应为你的T3中有重复定义，所以标间(标准)  关联出两倍数据，漆面的group by条件没映像，但是统计除了的数据就变二倍了

Answer 5

if object_id(N'Tempdb..#t') is not null drop table #T
if object_id(N'Tempdb..#t3') is not null drop table #T3CREATE TABLE #t(房间号 VARCHAR(10),期间 NVARCHAR(10),标准名称 NVARCHAR(20),单价 DECIMAL(10, 2),起期 DATETIME,止期 DATETIME,金额 DECIMAL(10, 2))
CREATE TABLE #t3(标准名称 NVARCHAR(20),收费项目 nvarchar(20))     
INSERT INTO #t
SELECT  '01' ,N'1季度',N'标间(标准)',0.70 ,'2017-01-01' ,'2017-03-31' ,997  UNION ALL
SELECT  '01' ,N'2季度',N'标间(标准)',1.20 ,'2017-04-01' ,'2017-06-30' ,1438 UNION ALL
SELECT  '02' ,N'1季度',N'家庭间(标准)',1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT  '03' ,N'1季度',N'家庭间(标准)',1.00 ,'2017-05-11' , '2017-12-31' , 600INSERT INTO #t3
SELECT  N'标间(标准)',N'标间' UNION ALL
SELECT  N'家庭间(标准)',N'家庭间'declare @yr int,@roomtype nvarchar(10),@startDate datetime,@endDate datetime 
set @yr=2017
set @roomtype=N'标间'
set @startdate='2017-03-14'
set @enddate ='2017-12/31'    
    
declare @cols nvarchar(4000),@sql nvarchar(4000)
    
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N' as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
       +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st
where sv.type='P'select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N' as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
       +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t 
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st and sv.number+st between year(@startDate) and  year(@endDate) and sv.number+st=@yr
where sv.type='P'set @sql=N'select t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,datediff(d,起期,止期)+1 as 总天数,'+@cols+char(13)
        +N' from #t as t inner join #t3 as t3 on t.标准名称=t3.标准名称 inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P'' 
and (hsv.number*200+sv.number) between 0 and datediff(d,起期,止期) '+char(13)
        +N'where t3.收费项目=N'''+@roomtype+''' AND hsv.number<=200 and sv.number<200 AND '+N'datediff(d,dateadd(d,hsv.number*200+sv.number,起期),'''+convert(varchar,@startDate,111)+N''')<=0 AND datediff(d,dateadd(d,hsv.number*200+sv.number,起期),'''+convert(varchar,@endDate,111)+N''')>=0'+char(13)
        +N'group by t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by t.房间号'
select @sql  
exec(@sql)
+--------+----+------+-------------------------+-------------------------+------+------------------+----+------+-----+--------------------+------+-----+-----+------+-----+--------------------+
| 房间号    | 期间 | 标准名称 | 收费项目                    | 单价                      | 起期   | 止期               | 金额 | 计算单价 | 总天数 | 年度1                | 天数1  | 金额1 | 年度2 | 天数2  | 金额2 | 年度1                |
+--------+----+------+-------------------------+-------------------------+------+------------------+----+------+-----+--------------------+------+-----+-----+------+-----+--------------------+
| 标间(标准) | 标间 | 0.7  | 2017-01-01 00:00:00:000 | 2017-03-31 00:00:00:000 | 997  | 11.0777777777777 | 90 | 2017 | 18  | 199.3999999999986  | 2018 | 0   | 0   | 2017 | 18  | 199.3999999999986  |
| 标间(标准) | 标间 | 1.2  | 2017-04-01 00:00:00:000 | 2017-06-30 00:00:00:000 | 1438 | 15.8021978021978 | 91 | 2017 | 91  | 1437.9999999999998 | 2018 | 0   | 0   | 2017 | 91  | 1437.9999999999998 |
+--------+----+------+-------------------------+-------------------------+------+------------------+----+------+-----+--------------------+------+-----+-----+------+-----+--------------------+

Answer 6

上面语句有重复代码declare @yr int,@roomtype nvarchar(10),@startDate datetime,@endDate datetime 
set @yr=2017
set @roomtype=N'标间'
set @startdate='2017-03-14'
set @enddate ='2017-12/31'    
    
declare @cols nvarchar(4000),@sql nvarchar(4000)
    
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N' as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
       +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t 
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st and sv.number+st between year(@startDate) and  year(@endDate) and sv.number+st=@yr
where sv.type='P'select @colsset @sql=N'select t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,datediff(d,起期,止期)+1 as 总天数,'+@cols+char(13)
        +N' from #t as t inner join #t3 as t3 on t.标准名称=t3.标准名称 inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P'' 
and (hsv.number*200+sv.number) between 0 and datediff(d,起期,止期) '+char(13)
        +N'where t3.收费项目=N'''+@roomtype+''' AND hsv.number<=200 and sv.number<200 AND '+N'datediff(d,dateadd(d,hsv.number*200+sv.number,起期),'''+convert(varchar,@startDate,111)+N''')<=0 AND datediff(d,dateadd(d,hsv.number*200+sv.number,起期),'''+convert(varchar,@endDate,111)+N''')>=0'+char(13)
        +N'group by t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by t.房间号'
select @sql  
exec(@sql)+-----+-----+--------+------+-----+-------------------------+-------------------------+------+------------------+-----+------+-----+--------------------+
| 房间号 | 期间  | 标准名称   | 收费项目 | 单价  | 起期                      | 止期                      | 金额   | 计算单价             | 总天数 | 年度1  | 天数1 | 金额1                |
+-----+-----+--------+------+-----+-------------------------+-------------------------+------+------------------+-----+------+-----+--------------------+
| 01  | 1季度 | 标间(标准) | 标间   | 0.7 | 2017-01-01 00:00:00:000 | 2017-03-31 00:00:00:000 | 997  | 11.0777777777777 | 90  | 2017 | 18  | 199.3999999999986  |
| 01  | 2季度 | 标间(标准) | 标间   | 1.2 | 2017-04-01 00:00:00:000 | 2017-06-30 00:00:00:000 | 1438 | 15.8021978021978 | 91  | 2017 | 91  | 1437.9999999999998 |
+-----+-----+--------+------+-----+-------------------------+-------------------------+------+------------------+-----+------+-----+--------------------+

Answer 7

@ch21st， 对不起，表述错了，5#的不是年度，是T#的期间，时间段是根据T#每条数据起期，止期时间段，有的就查出来，没有的不管，另外上面的查询结果年度1，天数1，金额1，我用我的实际数据带有的怎么都从7或8开始往后加的，都变成年度7，天数7，金额7开始的了，不知什么原因呢

Answer 8

我理解你第一个年度变7的意思，因为是前面6个年度被过滤掉了
我重新修改一下，现在不回出现那个情况，但如果你一次就查一个年度的数据，还在年度加序号干什么？反正就是一个年度
设置问题回到更初，甚至都用不到动态构造语句了，之所用动态语句是因为不知道数据范围会有多少年度从而不能确定要能返回的列数，但如果列能够确定就不需要这么处理了
另外下面语句也改成不要两次spt_values，而是自己构造一个序列，这个可以随便你基于什么表（虽然我还是基于spt_values），反正能返回你需要的最大number的行数就可以if object_id(N'Tempdb..#t') is not null drop table #t
if object_id(N'Tempdb..#t3') is not null drop table #t3
if object_id(N'Tempdb..#SeqNo') is not null drop table #SeqNo
CREATE TABLE #SeqNo(NUMBER INT)
INSERT INTO #SeqNo
SELECT row_number()over(ORDER BY getdate())-1
FROM master.dbo.spt_values as hsv,master.dbo.spt_values as sv 
WHERE hsv.type=sv.type and hsv.type='p' and hsv.number<=100 and sv.number<=100CREATE TABLE #t(房间号 VARCHAR(10),期间 NVARCHAR(10),标准名称 NVARCHAR(20),单价 DECIMAL(10, 2),起期 DATETIME,止期 DATETIME,金额 DECIMAL(10, 2))
CREATE TABLE #t3(标准名称 NVARCHAR(20),收费项目 nvarchar(20))
 
INSERT INTO #t
SELECT  '01' ,N'1季度',N'标间(标准)',0.70 ,'2017-01-01' ,'2017-03-31' ,997  UNION ALL
SELECT  '01' ,N'2季度',N'标间(标准)',1.20 ,'2017-04-01' ,'2017-06-30' ,1438 UNION ALL
SELECT  '02' ,N'1季度',N'家庭间(标准)',1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT  '03' ,N'1季度',N'家庭间(标准)',1.00 ,'2017-05-11' , '2017-12-31' , 600
 
INSERT INTO #t3
SELECT  N'标间(标准)',N'标间' UNION ALL
SELECT  N'家庭间(标准)',N'家庭间'
 
declare @yr int,@roomtype nvarchar(10),@startDate datetime,@endDate datetime 
set @yr=2018
set @roomtype=N'家庭间'
set @startdate='2018-01-01'
set @enddate ='2018-12/31'    
declare @cols nvarchar(4000),@sql nvarchar(4000)
     
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N' AS 年度'+ltrim(ROW_NUMBER()OVER(ORDER BY GETDATE())) 
            +N',sum(case when year(dateadd(d,sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(ROW_NUMBER()OVER(ORDER BY GETDATE())) 
            +N',sum(case when year(dateadd(d,sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(ROW_NUMBER()OVER(ORDER BY GETDATE())) 
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t 
) as t
inner join #SeqNo as sv on sv.number between 0 and ed-st and sv.number+st between year(@startDate) and  year(@endDate) and sv.number+st=@yrselect @cols
set @sql=N'select t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,max(sv.number)-min(sv.number)+1 as 总天数,'+@cols+char(13)
        +N' from #t as t inner join #t3 as t3 on t.标准名称=t3.标准名称 inner join #SeqNo as sv ON sv.number between 0 and datediff(d,起期,止期) '+char(13)
        +N'where t3.收费项目=N'''+@roomtype+''' AND '+N'datediff(d,dateadd(d,sv.number,起期),'''+convert(varchar,@startDate,111)+N''')<=0 AND datediff(d,dateadd(d,sv.number,起期),'''+convert(varchar,@endDate,111)+N''')>=0'+char(13)
        +N'group by t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by t.房间号'
select @sql
 
   
exec(@sql)
+-----+-----+---------+------+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-----------------+
| 房间号 | 期间  | 标准名称    | 收费项目 | 单价  | 起期                      | 止期                      | 金额   | 计算单价            | 总天数 | 年度1  | 天数1 | 金额1             |
+-----+-----+---------+------+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-----------------+
| 02  | 1季度 | 家庭间(标准) | 家庭间  | 1.2 | 2017-04-11 00:00:00:000 | 2018-04-10 00:00:00:000 | 1438 | 3.9397260273972 | 100 | 2018 | 100 | 393.97260273972 |
+-----+-----+---------+------+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-----------------+
如果参数没有固定的年度去掉构造列中的条件and sv.number+st=@yr
declare @yr int,@roomtype nvarchar(10),@startDate datetime,@endDate datetime 
set @yr=2018
set @roomtype=N'家庭间'
set @startdate='2017-01-01'
set @enddate ='2018-02-01'    --select * from #t
     
declare @cols nvarchar(4000),@sql nvarchar(4000)
     
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N' AS 年度'+ltrim(ROW_NUMBER()OVER(ORDER BY GETDATE())) 
            +N',sum(case when year(dateadd(d,sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(ROW_NUMBER()OVER(ORDER BY GETDATE())) 
            +N',sum(case when year(dateadd(d,sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(ROW_NUMBER()OVER(ORDER BY GETDATE())) 
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t 
) as t
inner join #SeqNo as sv on sv.number between 0 and ed-st and sv.number+st between year(@startDate) and  year(@endDate) --and sv.number+st=@yrselect @cols
set @sql=N'select t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,max(sv.number)-min(sv.number)+1 as 总天数,'+@cols+char(13)
        +N' from #t as t inner join #t3 as t3 on t.标准名称=t3.标准名称 inner join #SeqNo as sv ON sv.number between 0 and datediff(d,起期,止期) '+char(13)
        +N'where t3.收费项目=N'''+@roomtype+''' AND '+N'datediff(d,dateadd(d,sv.number,起期),'''+convert(varchar,@startDate,111)+N''')<=0 AND datediff(d,dateadd(d,sv.number,起期),'''+convert(varchar,@endDate,111)+N''')>=0'+char(13)
        +N'group by t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by t.房间号'
select @sql
 
exec(@sql)+-----+-----+---------+------+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-------------------+
| 房间号 | 期间  | 标准名称    | 收费项目 | 单价  | 起期                      | 止期                      | 金额   | 计算单价            | 总天数 | 年度1  | 天数1 | 金额1               | 年度2  | 天数2 | 金额2               |
+-----+-----+---------+------+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-------------------+
| 02  | 1季度 | 家庭间(标准) | 家庭间  | 1.2 | 2017-04-11 00:00:00:000 | 2018-04-10 00:00:00:000 | 1438 | 3.9397260273972 | 297 | 2017 | 265 | 1044.027397260258 | 2018 | 32  | 126.0712328767104 |
| 03  | 1季度 | 家庭间(标准) | 家庭间  | 1   | 2017-05-11 00:00:00:000 | 2017-12-31 00:00:00:000 | 600  | 2.5531914893617 | 235 | 2017 | 235 | 599.9999999999995 | 2018 | 0   | 0                 |
+-----+-----+---------+------+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-------------------+

Answer 9

 @ch21st，sql2000提示: 'ROW_NUMBER' 不是可以识别的 函数名
关键字 'as' 附近有语法错误另外年度还是要有的，可以在#T表里加入年度字段，以下面这条为例，年度是2017年，但2017年度的可能要收跨度是几年的费用，即起期到止期间要跨度几年，不用管是否合理，查询时要能根据年度，期间，标准名称进行模糊查询就行
SELECT  '02' ,N‘2017’,N'1季度',N'家庭间(标准)',1.20 ,'2017-04-11' ,'2018-04-10' ,1438

Answer 10

@ch21st，另外假如想把这个查询集写入到临时表要怎样写呢

Answer 11

很简单的计算，row_number函数要SQL2005以上才支持。用单价*第一年的天数得到第一年金额，然后用总金额减去这个金额得到第二年的金额，不要分摊计算到单价小数4位。

Answer 12

这个要用SQL2000来实现，而且可能会有第三年第四年这样比较麻烦。。，如果是SQL2005以上，做个表值函数，然后和T1表做cross apply关联，就很好做

Answer 13

写到后来忘记你的版本了，其实你应该理解代码背后真正的目的，知道目标后，因为到达目标的路径并不唯一，你完全可以自己改
比如这里的序号，如果ROW_NUMBER不支持，最笨的方法用个循环也可以实现啊。你要注意这个生成序号的逻辑你可以独立出来，不一定混在你这里的逻辑中，也不需要用临时表，用个正常表就行了。
比如下面就是用循环生成20000个号（我后面的逻辑是基于最小号为0）if object_id(N'Tempdb..#SeqNo') is not null drop table #SeqNo
CREATE TABLE #SeqNo(NUMBER INT primary key)
declare @i int
set @i=0
while @i<20000
begin
  insert into #SeqNo(NUMBER)values(@i)
  set @i=@i+1
end
另外你的年度我一直不知道是什么玩意，下面假如就是#t的一个普通字段if object_id(N'Tempdb..#t') is not null drop table #t
if object_id(N'Tempdb..#t3') is not null drop table #t3
 
CREATE TABLE #t(房间号 VARCHAR(10),年度 int,期间 NVARCHAR(10),标准名称 NVARCHAR(20),单价 DECIMAL(10, 2),起期 DATETIME,止期 DATETIME,金额 DECIMAL(10, 2))
CREATE TABLE #t3(标准名称 NVARCHAR(20),收费项目 nvarchar(20))
  
INSERT INTO #t
SELECT  '01',2017,N'1季度',N'标间(标准)',0.70 ,'2017-01-01' ,'2017-03-31' ,997  UNION ALL
SELECT  '01',2017 ,N'2季度',N'标间(标准)',1.20 ,'2017-04-01' ,'2017-06-30' ,1438 UNION ALL
SELECT  '01',2017 ,N'3季度',N'标间(标准)',1.20 ,'2017-07-01' ,'2017-09-30' ,1200 UNION ALL
SELECT  '01',2017 ,N'4季度',N'标间(标准)',1.20 ,'2017-10-01' ,'2017-12-31' ,1000 UNION ALL
SELECT  '02',2017 ,N'1季度',N'家庭间(标准)',1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT  '03',2017 ,N'1季度',N'家庭间(标准)',1.00 ,'2017-05-11' , '2017-12-31' , 600
  
INSERT INTO #t3
SELECT  N'标间(标准)',N'标间' UNION ALL
SELECT  N'家庭间(标准)',N'家庭间'
  
declare @yr int,@roomtype nvarchar(10),@startDate datetime,@endDate datetime 
set @yr=2017
set @roomtype=N'标间'
set @startdate='2017-01-01'
set @enddate ='2018-12-31'    
 
select sv.number
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t where 年度=@yr
) as t
inner join #SeqNo as sv on sv.number between 0 and ed-st and sv.number+st between year(@startDate) and  year(@endDate) 
 
 
declare @cols nvarchar(4000),@sql nvarchar(4000)
      
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N' AS 年度'+ltrim(sv.number+1) 
            +N',sum(case when year(dateadd(d,sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1) 
            +N',sum(case when year(dateadd(d,sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1) 
from (
   select min(year(起期)) as st,max(year(止期)) as ed from #t where 年度=@yr
) as t
inner join #SeqNo as sv on sv.number between 0 and ed-st and sv.number+st between year(@startDate) and  year(@endDate) 
 
select @cols
set @sql=N'select t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,max(sv.number)-min(sv.number)+1 as 总天数,'+@cols+char(13)
        +N' from #t as t inner join #t3 as t3 on t.标准名称=t3.标准名称 inner join #SeqNo as sv ON sv.number between 0 and datediff(d,起期,止期) '+char(13)
        +N'where t3.收费项目=N'''+@roomtype+''' AND '+N'datediff(d,dateadd(d,sv.number,起期),'''+convert(varchar,@startDate,111)+N''')<=0 AND datediff(d,dateadd(d,sv.number,起期),'''+convert(varchar,@endDate,111)+N''')>=0'+char(13)
        +N'group by t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by t.房间号'
select @sql
  
    
exec(@sql)还是那句话，你真正理解的SQL背后的逻辑，数据变化你自己就可以调整修改。下面的结果，虽然2018年没有数据，但因为在时间范围内也会列出+-----+-----+--------+------+-----+-------------------------+-------------------------+------+------------------+-----+------+-----+--------------------+------+-----+-----+
| 房间号 | 期间  | 标准名称   | 收费项目 | 单价  | 起期                      | 止期                      | 金额   | 计算单价             | 总天数 | 年度1  | 天数1 | 金额1                | 年度2  | 天数2 | 金额2 |
+-----+-----+--------+------+-----+-------------------------+-------------------------+------+------------------+-----+------+-----+--------------------+------+-----+-----+
| 01  | 1季度 | 标间(标准) | 标间   | 0.7 | 2017-01-01 00:00:00:000 | 2017-03-31 00:00:00:000 | 997  | 11.0777777777777 | 90  | 2017 | 90  | 996.999999999993   | 2018 | 0   | 0   |
| 01  | 2季度 | 标间(标准) | 标间   | 1.2 | 2017-04-01 00:00:00:000 | 2017-06-30 00:00:00:000 | 1438 | 15.8021978021978 | 91  | 2017 | 91  | 1437.9999999999998 | 2018 | 0   | 0   |
| 01  | 3季度 | 标间(标准) | 标间   | 1.2 | 2017-07-01 00:00:00:000 | 2017-09-30 00:00:00:000 | 1200 | 13.0434782608695 | 92  | 2017 | 92  | 1199.999999999994  | 2018 | 0   | 0   |
| 01  | 4季度 | 标间(标准) | 标间   | 1.2 | 2017-10-01 00:00:00:000 | 2017-12-31 00:00:00:000 | 1000 | 10.8695652173913 | 92  | 2017 | 92  | 999.9999999999996  | 2018 | 0   | 0   |
+-----+-----+--------+------+-----+-------------------------+-------------------------+------+------------------+-----+------+-----+--------------------+------+-----+-----+