当我使用合并查询插入时,我有Key1的tableA,datetime列,我在源中得到了重复的行
我们如何按日期对查询中的源进行过滤?时间具有最大
MERGE tableA AS t
USING (VALUES
('datakeyA1', 'datetime value'),
('datakeyB1', 'datetime value'),
('datakeyA1', 'max datetime value')
) AS s (Key1, datetime)
ON s.Key1 = t.Key1
WHEN MATCHED THEN
UPDATE
SET Val = s.datetime
WHEN NOT MATCHED THEN
INSERT (Key1, datetime)
VALUES (s.Key1, s.datetime);
当运行上述查询时,我得到:
MERGE语句试图更新或删除同一行不止一次。
是否有任何方法可以在不使用中间表的情况下,从所有值中仅查询这些行(">datakeyB1"、">日期时间值(、(">datakeyA1'"、">最大日期时间值'"(?
预期结果仅插入/更新datakeyB1、datakeyA1值。如果两个数据中的日期相同,则只得到一行。
MERGE tableA AS t
USING (VALUES
('datakeyB1', 'datetime value'),
('datakeyA1', 'max datetime value')
) AS s (Key1, datetime)
ON s.Key1 = t.Key1
WHEN MATCHED THEN
UPDATE
SET Val = s.datetime
WHEN NOT MATCHED THEN
INSERT (Key1, datetime)
VALUES (s.Key1, s.datetime);
您可以使用top 1 with ties
和order by row_number...
用select语句包装values
子句,如下所示:
MERGE tableA AS t
USING (
SELECT TOP 1 WITH TIES *
FROM
(VALUES
('datakeyA1', 'datetime value'),
('datakeyB1', 'datetime value'),
('datakeyA1', 'max datetime value')
) AS s (Key1, datetime)
ORDER BY ROW_NUMBER() OVER(PARTITION BY Key1 ORDER BY datetime DESC)
) s
ON s.Key1 = t.Key1
WHEN MATCHED THEN
UPDATE
SET Val = s.datetime
WHEN NOT MATCHED THEN
INSERT (Key1, datetime)
VALUES (s.Key1, s.datetime);