删除重复记录
问题描述：
我正在使用以下查询
SELECT SS.sightseeingId AS 'sID'
, SS.SightseeingName
, SS.displayPrice AS 'Price'
, SST.fromDate
FROM tblSightseeings SS INNER JOIN
tblSightseeingTours SST ON SS.sightseeingId = SST.sightseeingId
WHERE SS.isActive = 1 AND SS.isDisplayOnMainPage = 1
得到这样的结果
sID | SightseeingName | Price | fromDate
------------------------------------------------------------------------------
2 | Dinner Cruise Bateaux London (Premier) | 40 | 2009-04-01 00:00:00.000
2 | Dinner Cruise Bateaux London (Premier) | 40 | 2009-12-29 00:00:00.000
30 | Jack The Ripper, Ghosts and Sinister | 35.1 | 2009-04-01 00:00:00.000
30 | Jack The Ripper, Ghosts and Sinister | 35.1 | 2009-10-01 00:00:00.000
40 | Grand Tour of London | 0 | 2009-05-01 00:00:00.000
40 | Grand Tour of London | 0 | 2010-05-01 00:00:00.000
87 | Warwick, Stratford, Oxford and The | 25 | 2009-04-01 00:00:00.000
87 | Warwick, Stratford, Oxford and The | 25 | 2009-11-01 00:00:00.000
我想显示唯一记录 2 一次 30 一次 40 一次.重复记录是由于 SST.fromDate.
I want to display the unique records 2 one time 30 one time 40 one time. The duplicate records are due to SST.fromDate.
如何更正我的查询??
答
您可以尝试下一个查询:
You can try next query:
select SS.sightseeingId, SS.SightseeingName, SS.displayPrice, MAX(SST.fromDate)
from tblSightseeings SS inner join
tblSightseeingTours SST on SS.sightseeingId = SST.sightseeingId
where SS.isActive = 1 and SS.isDisplayOnMainPage = 1
GROUP by SS.sightseeingId, SS.SightseeingName, SS.displayPrice