如何SQL过滤重复数据

hrgpy

SELECT  Exa_Carinfo.Car_CPH as '车牌号', Exa_Carinfo.Car_XingHao as '车辆型号',Exa_Carinfo.Car_Vin as '车架号',CONVERT(VARCHAR(8),Exa_Carinfo.Car_DJDate,112) as '登记日期',Exa_Carinfo.Car_RLStr as '燃料种类',
Detect.JDetectLSBH as '报告单号',CONVERT(VARCHAR(8),Detect.Det_StartDate,112) as '检测日期',Detect.Det_TestTypeStr as '检测方法'
FROM Detect  join Exa_Carinfo  on Exa_Carinfo.ExamineID=Detect.ExamineID  
WHERE (Det_StartDate between '2013-07-22'and '2013-07-23')


我用上面的查询语句查出如下表格的内容，可是查询结果中还有车牌号一样，但报告单号不一样的记录，我想同一车牌号，的记录只要一个，报告单号取最后一次的，请问在上面的语句如何更改可实现，谢谢了

hrgpy

hrgpy

hrgpy

上面的查询结果

amazeyeli

[code=sql];WITH A AS
(
        SELECT *
        FROM Detect  
        join Exa_Carinfo  
        on Exa_Carinfo.ExamineID=Detect.ExamineID  
        WHERE (Det_StartDate between '2013-07-22'and '2013-07-23')
),
B AS
(
        SELECT Exa_Carinfo.Car_CPH,MAX(Detect.JDetectLSBH) AS Detect.JDetectLSBH_MAX
        FROM A
        GROUP BY Exa_Carinfo.Car_CPH
)
SELECT  
        Exa_Carinfo.Car_CPH as '车牌号',
        Exa_Carinfo.Car_XingHao as '车辆型号',
        Exa_Carinfo.Car_Vin as '车架号',
        CONVERT(VARCHAR(8),Exa_Carinfo.Car_DJDate,112) as '登记日期',
        Exa_Carinfo.Car_RLStr as '燃料种类',
        Detect.JDetectLSBH as '报告单号',
        CONVERT(VARCHAR(8),Detect.Det_StartDate,112) as '检测日期',
        Detect.Det_TestTypeStr as '检测方法'
FROM A
WHERE EXISTS
(SELECT 1 FROM B WHERE A.Exa_Carinfo.Car_CPH=B.Exa_Carinfo.Car_CPH AND A.Detect.JDetectLSBH=B.Detect.JDetectLSBH_MAX)[/code]