从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值。这可以通过查询吗?
您可以按日期(没有时间)分组,这应该适用于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