删除重复记录 [英] 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.

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

这篇关于删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！