从SQL查询中删除数据表中的重复项

本文关键字：删除 SQL 查询 数据表 | 更新日期: 2023-09-27 18:03:31

基本上我在sql

中有这段代码
SELECT DISTINCT A.fDate FROM tblReport_Date A 
LEFT JOIN tblReport_Type B ON A.ID_ReportType = B.ID 
LEFT JOIN tblReceiver C ON A.ID_Receiver = C.ID 
LEFT JOIN tblClients D ON A.ID_Client = D.ID 
WHERE A.fDate BETWEEN '1/1/2013 12:00:00 AM' AND '6/1/2014 12:00:00 AM'
AND B.fType = 'HCC' AND C.Receiver = '<test@test.com>'
AND D.fClientName = 'Radford'
现在，它会返回 
9/25/2013 8:00:00 AM
10/3/2013 12:00:00 PM
12/5/2013 10:00:00 AM
12/5/2013 12:00:00 AM

我想要的是像

这样的东西
9/25/2013 8:00:00 AM
10/3/2013 12:00:00 PM
12/5/2013 10:00:00 AM

或者更好的

9/25/2013 12:00:00 AM
10/3/2013 12:00:00 AM
12/5/2013 12:00:00 AM

我知道这在我当前的查询中是不可能的，因为

12/5/2013 10:00:00 和 12/5/2013 12:00:00是

有不同的TIME值。这可以通过查询吗?

从SQL查询中删除数据表中的重复项

您可以按日期(没有时间)分组，这应该适用于2005年以后的大多数SQL-Server版本:

SELECT fDate = DATEADD(day, DATEDIFF(day, 0, A.fDate), 0) 
FROM tblReport_Date A 
LEFT JOIN tblReport_Type B ON A.ID_ReportType = B.ID 
LEFT JOIN tblReceiver C ON A.ID_Receiver = C.ID 
LEFT JOIN tblClients D ON A.ID_Client = D.ID 
WHERE A.fDate BETWEEN '1/1/2013 12:00:00 AM' AND '6/1/2014 12:00:00 AM'
AND B.fType = 'HCC' AND C.Receiver = '<test@test.com>'
AND D.fClientName = 'Radford'
GROUP BY DATEADD(day, DATEDIFF(day, 0, A.fDate), 0)

或者在SQL Server 2008中转换为Date:

GROUP BY CAST(A.fDate AS DATE)

;WITH CTE_Temp ( SELECT DISTINCT A.fDate FROM tblReport_Date A LEFT JOIN tblReport_Type B ON A.ID_ReportType = B.ID LEFT JOIN tblReceiver C ON A.ID_Receiver = C.ID LEFT JOIN tblClients D ON A.ID_Client = D.ID WHERE A.fDate BETWEEN '1/1/2013 12:00:00 AM' AND '6/1/2014 12:00:00 AM' AND B.fType = 'HCC' AND C.Receiver = '<test@test.com>' AND D.fClientName = 'Radford' ) SELECT DISTINCT DATE(fDate) FROM CTE_Temp

相关文章：