求教二条SQL话语，十分感谢

求教二条SQL语句，十分感谢
第一个SQL语句
T1
编号日期级别
1001 2012-08-01 1
1001 2012-08-02 1
1001 2012-08-04 1
1001 2012-08-05 1
1002 2012-08-03 2
1002 2012-08-04 2
1002 2012-08-05 2
1002 2012-08-07 2
。。。

结果
编号日期天数级别
1001 2012-08-01 1 1
1001 2012-08-02 2 1
1001 2012-08-04 4 1
1001 2012-08-05 5 1
1002 2012-08-03 1 2
1002 2012-08-04 2 2
1002 2012-08-05 3 2
1002 2012-08-07 5 2
天数的意思是，根据相同编号的最早的那天开始计算，最早的那天为第一天，依次往下排，如果有天数间隔的，天数也随之进行间隔。

------解决方案--------------------

SQL code


----------------------------
-- Author  :TravyLee(努力工作中！！！)
-- Date    :2012-08-08 13:08:24
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--    Apr  2 2010 15:48:46 
--    Copyright (c) Microsoft Corporation
--    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
--
----------------------------
--> 测试数据：[T1]
if object_id('[T1]') is not null drop table [T1]
go 
create table [T1]([编号] int,[日期] datetime,[级别] int)
insert [T1]
select 1001,'2012-08-01',1 union all
select 1001,'2012-08-02',1 union all
select 1001,'2012-08-04',1 union all
select 1001,'2012-08-05',1 union all
select 1002,'2012-08-03',2 union all
select 1002,'2012-08-04',2 union all
select 1002,'2012-08-05',2 union all
select 1002,'2012-08-07',2
go

select *,
DATEDIFF(DD,
(select min([日期]) from T1 b where a.[编号]=b.[编号]),
[日期])+1 as 天数
from T1 a

/*
编号    日期    级别    天数
------------------------------------------------
1001    2012-08-01 00:00:00.000    1    1
1001    2012-08-02 00:00:00.000    1    2
1001    2012-08-04 00:00:00.000    1    4
1001    2012-08-05 00:00:00.000    1    5
1002    2012-08-03 00:00:00.000    2    1
1002    2012-08-04 00:00:00.000    2    2
1002    2012-08-05 00:00:00.000    2    3
1002    2012-08-07 00:00:00.000    2    5
*/

------解决方案--------------------

SQL code


--> 测试数据：[T1]
if object_id('[T1]') is not null drop table [T1]
go 
create table [T1]([编号] int,[日期] datetime,[级别] int)
insert [T1]
select 1001,'2012-08-01',1 union all
select 1001,'2012-08-02',1 union all
select 1001,'2012-08-04',1 union all
select 1001,'2012-08-05',1 union all
select 1002,'2012-08-03',2 union all
select 1002,'2012-08-04',2 union all
select 1002,'2012-08-05',2 union all
select 1002,'2012-08-07',2
go
--> 测试数据：[T2]
if object_id('[T2]') is not null drop table [T2]
go 
create table [T2]([级别] int,[起始天数1] int,[终止天数1] int,[金额1] int,[起始天数2] int,[终止天数2] int,[金额2] int)
insert [T2]
select 1,1,2,500,3,10,600 union all
select 2,1,2,700,3,10,800
go
with t
as(
select *,
DATEDIFF(DD,
(select min([日期]) from T1 b where a.[编号]=b.[编号]),
[日期])+1 as 天数
from T1 a
)
select a.*,
case when a.天数 between b.[起始天数1] and b.终止天数1 then [金额1] else b.金额2 end as 金额
from t a 
inner join T2 b
on a.级别=b.级别
go

/*
编号    日期    级别    天数    金额
-----------------------
1001    2012-08-01 00:00:00.000    1    1    500
1001    2012-08-02 00:00:00.000    1    2    500
1001    2012-08-04 00:00:00.000    1    4    600
1001    2012-08-05 00:00:00.000    1    5    600
1002    2012-08-03 00:00:00.000    2    1    700
1002    2012-08-04 00:00:00.000    2    2    700
1002    2012-08-05 00:00:00.000    2    3    800
1002    2012-08-07 00:00:00.000    2    5    800
*/

求教二条SQL话语，十分感谢

相关推荐