T-SQL:将每个组的第一行中的字段更新为一个值，并将其余字段设置为另一个值

Question

我有这个表：

ID   |Type|Date
17494|NULL|2013-09-04 15:00:00.000  
17577|NULL|2013-09-04 15:00:00.000  
17639|NULL|2013-09-04 17:00:00.000  
17701|NULL|2013-09-04 17:00:00.000

我想按日期分组，然后更新每个日期组第一行的"类型"字段并将其设置为"F"，其余的（可能是一个可以多个）将它们设置为"V"

结果：

ID   |Type|Date
17494|F   |2013-09-04 15:00:00.000  
17577|V   |2013-09-04 15:00:00.000  
17639|F   |2013-09-04 17:00:00.000  
17701|V   |2013-09-04 17:00:00.000

Answer 1

SQL2005+ 的解决方案：

DECLARE @MyTable TABLE
(
    ID      INT NOT NULL,
    [Type]  CHAR(1) NULL,
    [Date]  DATETIME NOT NULL 
);
INSERT  @MyTable (ID, [Type], [Date])
SELECT 17494, NULL, '2013-09-04 15:00:00.000' UNION ALL
SELECT 17577, NULL, '2013-09-04 15:00:00.000' UNION ALL
SELECT 17639, NULL, '2013-09-04 17:00:00.000' UNION ALL
SELECT 17701, NULL, '2013-09-04 17:00:00.000';
WITH MyTableWithRowNum
AS
(
    SELECT  *, ROW_NUMBER() OVER(PARTITION BY x.[Date] ORDER BY x.ID) AS RowNum
    FROM    @MyTable x
)
UPDATE  MyTableWithRowNum
SET     [Type] = CASE WHEN RowNum = 1 THEN 'F' ELSE 'V' END;
SELECT * FROM @MyTable;

结果：

ID          Type Date
----------- ---- -----------------------
17494       F    2013-09-04 15:00:00.000
17577       V    2013-09-04 15:00:00.000
17639       F    2013-09-04 17:00:00.000
17701       V    2013-09-04 17:00:00.000

Answer 2

试试这个：

UPDATE T
SET Type = CASE WHEN IDD = 1 THEN 'F' ELSE 'V' END
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Date ORDER BY ID) IDD , * FROM tbl) T;

SQL 小提琴演示

Answer 3

嗨，

没有直接查询来实现这一点。但我们可以通过变通方法实现这一目标

With #temptable as(
select Row_number() over( PARTITION BY date1 ORDER BY date1) Rank, *
from tablea)
update TableA set Type = 'F'
where id in (select Id from #temptable where Rank = 1)
update TableA set Type = 'V'
where Type is null
select * from TableA

我检查了SQLFiddle，它的工作 http://sqlfiddle.com/#!3/bfb05/6/0