SQL选择，包括分组依据和订单依据日期

Question

我正在使用SQLServer2008，我想知道我是否可以在一个select语句中完成查询，而不需要子查询。

如果记录中的某个字段在最近10个创建的记录中为true，我想将变量设置为true，如果该字段在最近10个记录中为true，则变量为true，如果为false，则变量为false，如果记录总数小于10，则变量也为false。

我的问题是，要获取最新创建的10条记录，我需要通过降序对用户进行排序，并对前10条进行筛选，因此我的查询应该如下所示，其中它不是有效的查询：

declare @MyVar bit
set @MyVar = 0

select top(10) @MyVar = 1 from MyTable
where SomeId = 1000 and SomeFlag = 1
group by SomeId
having count(SomeId) >= 10
order by CreatedDate

请给我你的建议。

下面是一个示例，假设我们有下表，并假设我想检查每个id的最新3条记录：

ID  Joined  CreatedDate
1   true    03/27/2013
1   false   03/26/2013
1   false   03/25/2013
1   true    03/24/2013
1   true    03/23/2013

2   true    03/22/2013
2   true    03/21/2013
2   true    03/20/2013
2   false   03/19/2013

3   true    03/18/2013
3   true    03/17/2013

对于id="1"，结果将为FALSE，因为最新创建的3条记录没有这3条记录中JOINED字段的值true。

对于id=“2”，结果将为TRUE，因为最近创建的3条记录在这3条记录中具有TRUE join字段。

对于id=“3”，结果将为假，因为要检查的最新创建的记录必须至少有3条记录。

Answer 1

在SQLServer2008中，您可以使用CTE和ROW_NUMBER（）排名函数来代替子查询

 ;WITH cte AS
 (      
  SELECT ID, CAST(Joined AS int) AS Flag, 
         ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CreatedDate DESC) AS rn
  FROM dbo.test63 t
  )
  SELECT ID, CASE WHEN SUM(Flag) != 3 THEN 0 ELSE 1 END AS Flag
  FROM cte
  WHERE rn <= 3
  GROUP BY ID

SQLFiddle上的演示

Answer 2

（在OP指定2008之前给出的答案。以下仅适用于2012年）

此查询（对于每个ID值）给出最后10行中标志等于1的行数。如果需要的话，可以很简单地将其筛选为计数为10的行，并将其限制为单个ID值。

如果没有更好的样本数据，我现在就到此为止：

;with Vals as (
    select
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CreatedDate DESC) as rn,
        SUM(CASE WHEN Flag = 1 THEN 1 ELSE 0 END)
                 OVER (PARTITION BY ID
                       ORDER BY CreatedDate ASC
                       ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as Cnt
    from
        T1
)
select * from Vals where rn = 1

（这确实取决于SQLServer 2012版本的OVER子句-但是您没有指定哪个版本）

结果：

ID          Flag  CreatedDate             rn                   Cnt
----------- ----- ----------------------- -------------------- -----------
1           1     2012-01-12 00:00:00.000 1                    10
2           1     2012-01-12 00:00:00.000 1                    9
3           1     2012-01-12 00:00:00.000 1                    6

（只有ID 1符合您的标准）

样本数据：

create table T1 (ID int not null,Flag bit not null,CreatedDate datetime not null)
insert into T1 (ID,Flag,CreatedDate) values
(1,1,'20120101'),
(1,0,'20120102'),
(1,1,'20120103'),
(1,1,'20120104'),
(1,1,'20120105'),
(1,1,'20120106'),
(1,1,'20120107'),
(1,1,'20120108'),
(1,1,'20120109'),
(1,1,'20120110'),
(1,1,'20120111'),
(1,1,'20120112'),

(2,1,'20120101'),
(2,1,'20120102'),
(2,1,'20120103'),
(2,1,'20120104'),
(2,1,'20120105'),
(2,1,'20120106'),
(2,0,'20120107'),
(2,1,'20120108'),
(2,1,'20120109'),
(2,1,'20120110'),
(2,1,'20120111'),
(2,1,'20120112'),

(3,1,'20120107'),
(3,1,'20120108'),
(3,1,'20120109'),
(3,1,'20120110'),
(3,1,'20120111'),
(3,1,'20120112')