sql - 使用 TSQL SQL Server (2008 R2) 有条件地选择行

Question

我已经盯着这个看了好几个小时了，想不出一个“优雅”的基于集合的方法来获取我需要的结果集……

这是我的示例数据(我的真实数据可能超过 1,000,000 行)...

DECLARE @t AS TABLE (ID int,ID1 nvarchar(15),[DATE] date,PERIOD int,[TYPE] nchar(1));

INSERT INTO @t (ID,ID1,[DATE],PERIOD,[TYPE])
VALUES
(1,N'NUM1','2016-01-01',1,N'B'),
(2,N'NUM1','2016-01-01',2,N'A'),
(3,N'NUM1','2016-01-01',3,N'A'),
(4,N'NUM1','2016-01-01',4,N'B'),
(5,N'NUM1','2016-01-01',4,N'A'),
(6,N'NUM1','2016-01-01',5,N'A'),

(7,N'NUM1','2016-01-02',1,N'A'),
(8,N'NUM1','2016-01-02',2,N'A'),
(9,N'NUM1','2016-01-02',3,N'A'),
(10,N'NUM1','2016-01-02',4,N'A'),
(11,N'NUM1','2016-01-02',5,N'A'),

(12,N'NUM2','2016-01-01',1,N'A'),
(13,N'NUM2','2016-01-01',1,N'B'),
(14,N'NUM2','2016-01-01',2,N'A'),
(15,N'NUM2','2016-01-01',3,N'A'),
(16,N'NUM2','2016-01-01',4,N'B'),
(17,N'NUM2','2016-01-01',4,N'A'),
(18,N'NUM2','2016-01-01',5,N'A'),

(19,N'NUM2','2016-01-02',1,N'A'),
(20,N'NUM2','2016-01-02',2,N'B'),
(21,N'NUM2','2016-01-02',3,N'A'),
(22,N'NUM2','2016-01-02',4,N'A'),
(23,N'NUM2','2016-01-02',4,N'B'),
(24,N'NUM2','2016-01-02',5,N'A');

这是我试图获得的结果集...

1,'NUM1','2016-01-01',1,'B'
2,'NUM1','2016-01-01',2,'A'
3,'NUM1','2016-01-01',3,'A'
5,'NUM1','2016-01-01',4,'A'
6,'NUM1','2016-01-01',5,'A'

7,'NUM1','2016-01-02',1,'A'
8,'NUM1','2016-01-02',2,'A'
9,'NUM1','2016-01-02',3,'A'
10,'NUM1','2016-01-02',4,'A'
11,'NUM1','2016-01-02',5,'A'

12,'NUM2','2016-01-01',1,'A'
14,'NUM2','2016-01-01',2,'A'
15,'NUM2','2016-01-01',3,'A'
17,'NUM2','2016-01-01',4,'A'
18,'NUM2','2016-01-01',5,'A'

19,'NUM2','2016-01-02',1,'A'
20,'NUM2','2016-01-02',2,'B'
21,'NUM2','2016-01-02',3,'A'
22,'NUM2','2016-01-02',4,'A'
24,'NUM2','2016-01-02',5,'A'

简单地说，每天有 5 个时段。它们可以是 A 型或 B 型。我需要获得 A 型。但是如果没有A类型，我需要得到B类型......(写出来的时候听起来很简单..，但我的大脑想不出合适的东西)

请让我摆脱痛苦..

Answer 1

您可以为此使用 ROW_NUMBER:

SELECT ID, ID1, [DATE], PERIOD, [TYPE]
FROM (
   SELECT ID, ID1, [DATE], PERIOD, [TYPE],  
          ROW_NUMBER() OVER (PARTITION BY ID1, [DATE], PERIOD 
                             ORDER BY [TYPE]) AS rn
   FROM @t) AS t
WHERE t.rn = 1

在 ROW_NUMBER 的 OVER 子句中使用 ORDER BY [TYPE] 放置 'A' 记录在 'B' 记录之上。如果给定的 ID1, [DATE], PERIOD 没有 'A' 记录，则为 B 记录分配 rn = 1 。