sql - T-SQL : Using OVER and PARTITION BY

Question

我有以下数据

 |  Item  | Value |    Date   |
 ------------------------------
 |   1    |  10   | 01.01.2010
 |   1    |  20   | 02.01.2010
 |   1    |  30   | 03.01.2010
 |   1    |  40   | 04.01.2010
 |   1    |  50   | 05.01.2010
 |   1    |  80   | 10.01.2010
 |   2    |  30   | 04.01.2010
 |   2    |  60   | 06.01.2010
 |   2    |  70   | 07.01.2010
 |   2    |  80   | 08.01.2010
 |   2    |  100  | 09.01.2010

以及以下声明

SELECT   Item, Value, MIN(Date) OVER (PARTITION BY Item) 
FROM     Data
WHERE    Value >= 50   

我得到以下结果

 |  Item  | Value |    Date   |
 ------------------------------
 |   1    |  50   | 05.01.2010
 |   1    |  80   | 05.01.2010
 |   2    |  60   | 06.01.2010
 |   2    |  70   | 06.01.2010
 |   2    |  80   | 06.01.2010
 |   2    |  100  | 06.01.2010

但我需要的是这个

 |  Item  | Value |    Date   |
 ------------------------------
 |   1    |  10   | 05.01.2010
 |   1    |  20   | 05.01.2010
 |   1    |  30   | 05.01.2010
 |   1    |  40   | 05.01.2010
 |   1    |  50   | 05.01.2010
 |   1    |  80   | 05.01.2010
 |   2    |  30   | 06.01.2010
 |   2    |  60   | 06.01.2010
 |   2    |  70   | 06.01.2010
 |   2    |  80   | 06.01.2010
 |   2    |  100  | 06.01.2010

是否有任何快速解决方案可以通过一条语句而不需要自连接来实现此目的？

谢谢:)

Answer 1

如果没有自加入，请尝试以下操作:

DECLARE @YourTable table (item int,value int, Date datetime)
INSERT @YourTable VALUES (1    ,  10   , '01/01/2010')
INSERT @YourTable VALUES (1    ,  20   , '02/01/2010')
INSERT @YourTable VALUES (1    ,  30   , '03/01/2010')
INSERT @YourTable VALUES (1    ,  40   , '04/01/2010')
INSERT @YourTable VALUES (1    ,  50   , '05/01/2010')
INSERT @YourTable VALUES (1    ,  80   , '10/01/2010')
INSERT @YourTable VALUES (2    ,  30   , '04/01/2010')
INSERT @YourTable VALUES (2    ,  60   , '06/01/2010')
INSERT @YourTable VALUES (2    ,  70   , '07/01/2010')
INSERT @YourTable VALUES (2    ,  80   , '08/01/2010')
INSERT @YourTable VALUES (2    ,  100  , '09/01/2010')


SELECT   Item, Value, MIN(CASE WHEN Value >= 50 THEN Date ELSE NULL END) OVER (PARTITION BY Item) 
FROM     @YourTable

输出:

Item        Value       
----------- ----------- -----------------------
1           10          2010-05-01 00:00:00.000
1           20          2010-05-01 00:00:00.000
1           30          2010-05-01 00:00:00.000
1           40          2010-05-01 00:00:00.000
1           50          2010-05-01 00:00:00.000
1           80          2010-05-01 00:00:00.000
2           30          2010-06-01 00:00:00.000
2           60          2010-06-01 00:00:00.000
2           70          2010-06-01 00:00:00.000
2           80          2010-06-01 00:00:00.000
2           100         2010-06-01 00:00:00.000
Warning: Null value is eliminated by an aggregate or other SET operation.

(11 row(s) affected)