删除重复记录(Removing duplicate records)

我使用下面的查询

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 。

如何纠正我的查询？

Answer 1:

你可以试试下面的查询：

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

Answer 2:

那么，记录实际上并没有复制，因为日期不同。你可以这样做：

select  SS.sightseeingId, SS.SightseeingName, SS.displayPrice,  MIN(SST.fromDate) AS 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

Answer 3:

try this (example will return the highest date in the group):

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

Depending on what date you want to show you can select the highest using MAX or the lowest using MIN. If you have other criteria you may need to do a subquery.