插入排除第二个，从现有记录中添加第一个

Question

我想找到比我的解决方案更有效的方法。所以问题是这样的： 我想从 select 语句进行批量插入。 （背景信息：我正在使用 MSSQL 2005）

选择示例：

SELECT number, amount, year, modifiedDate, itm, city, c, d, e, f.... FROM X
JOIN Y ....

因此，如果结果是

...
Num Amount Year ModifiedDate Itm City ... ...
1   100    2011 01-01-2011   2   Amsterdam .. ..
1   100    2011 01-02-2011   5   Den Haag .. ..
2   4560   2011 01-02-2011   6   Amsterdam .. .. 
33   456    2010 01-02-2011   12  Leiden .. ..
22   456    2010 01-02-2011   12  Leiden .. ..
....

在我想要的目标中：

...
Num Amount Year ModifiedDate Itm City ... ...
1   100    2011 01-02-2011   5   Den Haag .. ..
2   4560   2011 01-02-2011   6   Amsterdam .. .. 
33   456    2010 01-02-2011   12  Leiden .. ..
22   456    2010 01-02-2011   12  Leiden .. ..
....

没有此记录： ( 1 100 2011 01-01-2011 2 阿姆斯特丹)

我想插入第一个来自目标表中的这两个。我还有其他来自选择的记录也应该插入。因此，在这种情况下，我想采用第一个，其中数字、金额和年份按 ModifiedDate DESC 顺序相同。这就是我想做的。我已经使用 Cursor 做了一个解决方案，但应该有更好的方法。

Answer 1

感谢您的所有回复。我有一个不同的想法，它比老式光标快得多，而且我认为它符合我正在寻找的东西，所以我也在这里分享它。

--Test Data
DECLARE @sourceTable TABLE(number int,amount int, yr int, modifiedDate datetime, city nvarchar(100))
DECLARE @destTable TABLE(number int,amount int, yr int, modifiedDate datetime, city nvarchar(100))  

INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (1,100,2011,'01 Jan 2011', 'aaa')
INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (1,100,2011,'02 Jan 2011', 'bbb') 
INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (2,4560,2011,'02 Jan 2011', 'ccc') 
INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (33,456,2010,'02 Jan 2011', 'ddd') 
INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (22,456,2010,'02 Jan 2011', 'ddd') 

--Query
INSERT INTO @destTable
SELECT * FROM @sourceTable
WHERE CAST(number AS NVARCHAR(100)) + '_' + CAST(amount AS NVARCHAR(100)) + '_' + CAST(yr AS NVARCHAR(100)) + '_' + CONVERT(NVARCHAR(100), modifiedDate,121)
IN
(
SELECT CAST(number AS NVARCHAR(100)) + '_' + CAST(amount AS NVARCHAR(100)) + '_' + CAST(yr AS NVARCHAR(100)) + '_' + CONVERT(NVARCHAR(100), MAX(modifiedDate),121)
FROM @sourceTable  
GROUP BY number, amount, yr 
)  
--Results 
SELECT * FROM @destTable 

Answer 2

这应该会让您更接近您所需要的：

DECLARE @sourceTable TABLE(number int,amount int, yr int, modifiedDate datetime, itm int, City varchar(20))
DECLARE @destTable TABLE(number int,amount int, yr int, modifiedDate datetime, itm int, City varchar(20))

INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (1,100,2011,'01-01-2011',2,'Amsterdam')
INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (1,100,2011,'01-02-2011',5,'Den Haag')
INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (2,4560,2011,'01-02-2011',6,'Amsterdam')
INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (33,456,2010,'01-02-2011',12,'Leiden')
INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (22,456,2010,'01-02-2011',12,'Leiden')

;WITH CTE AS
(
    SELECT number, amount, yr, modifiedDate
    , ROW_NUMBER() OVER (PARTITION BY number, amount, yr ORDER BY modifiedDate DESC) AS itemRank 

    FROM @sourceTable 
    GROUP BY number, amount, yr, modifiedDate
)
INSERT INTO @destTable (number, amount, yr, modifiedDate, Itm, City )
SELECT st.number, st.amount, st.yr, st.modifiedDate, st.Itm, st.City 
FROM @sourceTable st
INNER JOIN cte ON st.number = cte.number
AND st.amount = cte.amount
AND st.yr = cte.yr
AND st.modifiedDate = cte.modifiedDate
WHERE itemRank = 1
ORDER BY modifiedDate DESC

SELECT * FROM @destTable

Answer 3

如果我理解您想要正确执行的操作，我认为您需要对前三列进行分组，然后从 modifiedDate 列中选择 MAX 值。

如果我误解了，请告诉我。

SELECT   
         number, 
         amount,
         year,
         MAX(modifiedDate)
FROM     
         X
GROUP BY
         number, amount, year

如果您还在查询的 INSERT 部分寻求帮助，请提供有关您当前正在执行的操作的更多详细信息。

Answer 4

按所有列进行分组即可实现此目的。

例子：

--Test Data
DECLARE @sourceTable TABLE(number int,amount int, yr int, modifiedDate datetime)
DECLARE @destTable TABLE(number int,amount int, yr int, modifiedDate datetime)

INSERT INTO @sourceTable (number, amount, yr, modifiedDate ) VALUES (1,100,2011,'01 Jan 2011')
INSERT INTO @sourceTable (number, amount, yr, modifiedDate ) VALUES (1,100,2011,'01 Jan 2011')
INSERT INTO @sourceTable (number, amount, yr, modifiedDate ) VALUES (1,100,2011,'03 Jan 2011')
INSERT INTO @sourceTable (number, amount, yr, modifiedDate ) VALUES (1,100,2011,'02 Jan 2011')

--Query
INSERT INTO @destTable(number, amount, yr, modifiedDate )
SELECT number, amount, yr, modifiedDate
FROM @sourceTable 
GROUP BY number, amount, yr, modifiedDate
ORDER BY modifiedDate DESC

--Results
SELECT * FROM @destTable