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')
我用上面的查询语句查出如下表格的内容,可是查询结果中还有车牌号一样,但报告单号不一样的记录,我想同一车牌号,的记录只要一个,报告单号取最后一次的,请问在上面的语句如何更改可实现,谢谢了
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')
我用上面的查询语句查出如下表格的内容,可是查询结果中还有车牌号一样,但报告单号不一样的记录,我想同一车牌号,的记录只要一个,报告单号取最后一次的,请问在上面的语句如何更改可实现,谢谢了
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')
and not exists(select 1 from Exa_Carinfo t where t.Car_CPH=Exa_Carinfo.Car_CPH and t.ExamineID<Exa_Carinfo.ExamineID)
;with t as
(
SELECT rn=row_number()over(partition by Exa_Carinfo.Car_CPH order by Detect.JDetectLSBH desc), 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')
)
select * from t where rn=1
b.Car_XingHao as '车辆型号',
b.Car_Vin as '车架号',
CONVERT(VARCHAR(8),b.Car_DJDate,112) as '登记日期',
b.Car_RLStr as '燃料种类',
a.JDetectLSBH as '报告单号',
CONVERT(VARCHAR(8),a.Det_StartDate,112) as '检测日期',
a.Det_TestTypeStr as '检测方法'
FROM Detect a
join Exa_Carinfo b on b.ExamineID=a.ExamineID
WHERE Det_StartDate between '2013-07-22'and '2013-07-23'
and not exists
(select 1 from Detect c
join Exa_Carinfo d on d.ExamineID=c.ExamineID
where Det_StartDate between '2013-07-22'and '2013-07-23'
and d.Car_CPH=b.Car_CPH and c.JDetectLSBH>a.JDetectLSBH)