CREATE TABLE [dbo].[Results](
    [RID] [int] IDENTITY(1,1) NOT NULL,
    [LID] [int] NOT NULL,
    [HID] [int] NOT NULL,
    [Result] [bit] NOT NULL,
 CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED 
(
    [HID] ASC,
    [LID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


表数据

RID    LID    HID    Result
2    1    2003001    1
3    2    2003001    1
4    3    2003001    1
5    4    2003001    1
6    5    2003001    1
7    6    2003001    1
8    7    2003001    1
9    8    2003001    1
10    9    2003001    1
11    10    2003001    1
12    11    2003001    1
13    12    2003001    1
14    13    2003001    1
15    14    2003001    1
16    15    2003001    1
17    16    2003001    1
18    17    2003001    1
19    18    2003001    1
20    19    2003001    1
21    20    2003001    1
......
12872    1    2003002    1
12873    2    2003002    1
12874    3    2003002    1
12875    4    2003002    1
12876    5    2003002    1
12877    6    2003002    1
12878    7    2003002    1
12879    8    2003002    1
12880    9    2003002    1
12881    10    2003002    1
.............


要求统计结果

with [temps] as
    (
    select row_number() over(order by RID Desc) as TID,HID,LID,Result  from Results where LID = 1
    )
     select sum(case when TID>=1 and TID <=10 then 1 else 0 end) as [C010]
            
    from temps where Result = 1 
这条语句写统计的是 最后一条的数据.可是以前的统计的数据呢?

假如HID 有10期
则统计的有10条记录
如下
3
5
7
8
5
8
9
10
5
6

with [temps] as
    (
    select row_number() over(PARTITION BY LID order by RID Desc) as TID,HID,LID,Result  from Results
    )
     select Lid,sum(case when TID>=1 and TID <=10 then 1 else 0 end) as [C010]
           
    from temps where Result = 1
    GROUP BY Lid