求sql

[liqiyou]

原始数据表：
id 病人ID 记录日期 体温
1 1001 2018-6-1 38
2 1001 2018-6-2 37
3 1001 2018-6-3 37.2
4 1001 2018-6-4 36.8
5 1001 2018-7-6 37.2
6 1001 2018-7-7 37.5
7 1001 2018-7-8 36.8
8 1001 2018-7-9 37.8
结果是：
id 病人ID 记录日期 体温 初始Id
1 1001 2018-6-1 38 1
2 1001 2018-6-2 37 1
3 1001 2018-6-3 37.2 1
4 1001 2018-6-4 36.8 1
5 1001 2018-7-6 37.2 5
6 1001 2018-7-7 37.5 5
7 1001 2018-7-8 36.8 5
8 1001 2018-7-9 37.8 5
结果是一个视图，通过一个sql语句，生成初始Id列。
初始ID是指本记录的记录日期连续时间段的最小值，比如：前四条：每天记录的初始ID都是1.因为从6－1开始连续的。
哪位高手指点一下。

[Mr_Nice]

--> 测试数据：@ttt
declare @ttt table([id] int,[病人ID] int,[记录日期] datetime,[体温] numeric(3,1))
INSERT @ttt
select 1,1001,'2018-6-1',38 union all
select 2,1001,'2018-6-2',37 union all
select 3,1001,'2018-6-3',37.2 union all
select 4,1001,'2018-6-4',36.8 union all
select 5,1001,'2018-7-6',37.2 union all
select 6,1001,'2018-7-7',37.5 union all
select 7,1001,'2018-7-8',36.8 union all
select 8,1001,'2018-7-9',37.8

SELECT  * ,
        ( SELECT    max(id)
          FROM      @ttt AS B
          WHERE     B.记录日期 <= A.记录日期
                    AND not EXISTS ( SELECT 1
                                     FROM   @ttt AS C
                                     WHERE  B.记录日期 = DATEADD(dd, 1, C.记录日期) )
        ) AS GRP
FROM    @ttt AS A;


/*
id	病人ID	记录日期	体温	GRP
1	1001	2018-06-01 00:00:00.000	38.0	1
2	1001	2018-06-02 00:00:00.000	37.0	1
3	1001	2018-06-03 00:00:00.000	37.2	1
4	1001	2018-06-04 00:00:00.000	36.8	1
5	1001	2018-07-06 00:00:00.000	37.2	5
6	1001	2018-07-07 00:00:00.000	37.5	5
7	

[zhaoanle]

Create table #t(id int , 病人ID int, 记录日期 datetime, 体温 dec(10,1))

insert #t
select
1 , 1001 , '2018-6-1' , 38
union select
2 , 1001 , '2018-6-2' , 37
union select
3 , 1001 , '2018-6-3' , 37.2
union select
4 , 1001 , '2018-6-4' , 36.8
union select
5 , 1001 , '2018-7-6' , 37.2
union select
6 , 1001 , '2018-7-7' , 37.5
union select
7 , 1001 , '2018-7-8' , 36.8
union select
8 , 1001 , '2018-7-9' , 37.8




select IDENTITY(Int,1,1) as SN, * into #tt from #t ORDER BY 病人ID,记录日期,ID


select a.id,a.病人ID,a.记录日期,a.体温,b.ID as 初始ID from #tt a left join (select min(ID)as ID,病人ID,datediff(dd,'2018-01-01',记录日期)-SN as SN from #tt group by 病人ID,datediff(dd,'2018-01-01',记录日期)-SN) b on a.病人ID= b.病人ID
and datediff(dd,'2018-01-01',a.记录日期)-a.SN = b.SN


drop table #tt,#t

[shinger126]

Create table #t(id int , 病人ID int, 记录日期 datetime, 体温 dec(10,1))

insert #t
select
1 , 1001 , '2018-6-1' , 38
union select
2 , 1001 , '2018-6-2' , 37
union select
3 , 1001 , '2018-6-3' , 37.2
union select
4 , 1001 , '2018-6-4' , 36.8
union select
5 , 1001 , '2018-7-6' , 37.2
union select
6 , 1001 , '2018-7-7' , 37.5
union select
7 , 1001 , '2018-7-8' , 36.8
union select
8 , 1001 , '2018-7-9' , 37.8

;WITH t AS (
SELECT DATEDIFF(DAY,'2018-01-01',记录日期)-DAY(记录日期) tid,* FROM #t ),
m AS (SELECT tid,病人ID,MIN(id) id FROM t GROUP BY tid,病人ID)
SELECT m.id,t.病人ID,t.记录日期,t.体温
FROM t,m WHERE t.tid=m.tid
AND t.病人ID=m.病人ID