计数更新的记录

Question

我有以下SQL Server 2008的表：

CREATE TABLE tbl (ID INT, dtIn DATETIME2, dtOut DATETIME2, Type INT, nID INT) 
INSERT tbl VALUES 
(1, '05:00', '10:00', 1, 1), --will be changed 
(2, '08:00', '16:00', 2, 1), 
(3, '02:00', '08:00', 1, 1), --will be changed 
(4, '07:30', '11:00', 1, 1) 

，我用下面的SQL来更新部分重叠记录：

UPDATE tbl 
SET dtOut = COALESCE((
    SELECT MIN(dtIn) 
    FROM tbl as t2 
    WHERE t2.type = tbl.type AND 
      t2.id <> tbl.id AND 
      t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut 
    ), dtOut) 
WHERE nID=1; 

SELECT ROWCOUNT_BIG(); 

用最后一个SELECT认为检索更新的记录数，但它不起作用。

所以我的问题是，我该如何重写这个语句来获取实际更新的记录数？ （在上面的数据表中有2个）。

Answer 1

1. 就在最后SELECT

UPDATE tbl 
SET dtOut = COALESCE((
    SELECT MIN(dtIn) 
    FROM tbl as t2 
    WHERE t2.type = tbl.type AND 
      t2.id <> tbl.id AND 
      t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut 
    ), dtOut) 
WHERE nID=1 
SELECT @@ROWCOUNT; 

删除分号......现在返回的东西 - 4 - 这是因为更新运行在全部行，而不仅仅是你需要的2。

2. 越来越近 - 以下只是运行在3行是1型：

update x 
set x.dtout = y.mn 
from tbl x 
inner join 
    ( 
    SELECT t1.type, min(t1.dtIn) mn 
    FROM tbl t1 
    inner join tbl t2 
    on 
     t1.type = t2.type AND 
     t1.id <> t2.id AND 
     t1.dtIn >= t2.dtIn AND 
     t1.dtIn < t2.dtOut 
    group by t1.type 
) y 
    on 
     x.type = y.type 
SELECT @@ROWCOUNT; 

HERE IS THE SQL FIDDLE OF THE ABOVE

3. 感谢@Gilm我炒什么，我希望没问题;它非常类似于Gilm的CTE答案使用的逻辑：

update x 
set x.dtout = y.mn 
from tbl x 
inner join 
    ( 
    SELECT t1.id, min(t2.dtIn) mn 
    FROM tbl t1 
    inner join tbl t2 
    on 
     t1.type = t2.type AND 
     t1.id <> t2.id AND 
     t2.dtIn >= t1.dtIn AND 
     t2.dtIn < t1.dtOut 
    group by t1.id 
) y 
    on 
     x.id = y.id 
SELECT @@ROWCOUNT; 
SELECT * FROM tbl; 

ON SQL FIDDLE HERE

Answer 2

SQL Server将更新原始示例中的所有行。怎么样是这样的：

WITH CTE AS (
    SELECT t1.ID, MIN(t2.dtIn) as New_dtOut 
    FROM tbl as t1 
    LEFT JOIN tbl AS t2 ON t2.type = t1.type AND t2.id <> t1.id 
      AND t2.dtIn >= t1.dtIn AND t2.dtIn < t1.dtOut 
    WHERE t1.nID = 1 
    GROUP BY t1.ID 
) 
UPDATE t1 SET dtOut = t2.New_dtOut 
FROM tbl t1 
JOIN CTE t2 on t2.ID = t1.ID 
WHERE t1.dtOut <> t2.New_dtOut 

SELECT ROWCOUNT_BIG(); 

Answer 3

WHERE nID=1是决定多少行会被更新。即使你自己分配dtOut的行也会被更新和计数。

您可以使用output子句捕获表变量中的更新行，然后对dtOut已更改的行进行计数。

DECLARE @T TABLE 
(
    dtOutOld DATETIME2, 
    dtOutNew DATETIME2 
) 

UPDATE tbl 
SET dtOut = COALESCE((
    SELECT MIN(dtIn) 
    FROM tbl as t2 
    WHERE t2.type = tbl.type AND 
      t2.id <> tbl.id AND 
      t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut 
    ), dtOut) 
OUTPUT deleted.dtOut, inserted.dtOut INTO @T 
WHERE nID=1 

SELECT COUNT(*) 
FROM @T 
WHERE dtOutOld <> dtOutNew 

SQL-Fiddle