34,590
社区成员
发帖
与我相关
我的任务
分享
select top 1 mCarNumber,mLine from SS_Main where mCarNumber='12345' order by mDate desc,mIndex desc
select * from tb t where mCarNumber=(select max(mCarNumber) from tb where mid=t.mid)
--> 测试数据: [SS_Main]
if object_id('[SS_Main]') is not null drop table [SS_Main]
create table [SS_Main] (mId varchar(13),mDate varchar(10),mIndex int,mCarNumber int,mLine varchar(14))
insert into [SS_Main]
select 'YCZY101202080','2010-12-02',80,74213,'保定-徐州-凤台' union all
select 'YCZY101202081','2010-12-04',81,74213,'凤台-合肥' union all
select 'YCZY101206176','2010-12-06',176,74213,'合肥-邢台' union all
select 'YCZY101207221','2010-12-07',221,74213,'邢台-保定' union all
select 'YCZY101210220','2010-12-10',220,74213,'保定-高碑店' union all
select 'YCGK101201025','2010-12-01',25,74791,'黄岛-金华' union all
select 'YCGK101202070','2010-12-02',70,74791,'金华-南京' union all
select 'YCGK101203096','2010-12-03',96,74791,'南京-淄博' union all
select 'YCGK101205195','2010-12-05',195,74791,'淄博-南京' union all
select 'YCGK101206161','2010-12-06',161,74791,'南京-杭州-萧山'
--开始查询
select * from SS_Main a where not exists(
select 1 from SS_Main where mCarNumber=a.mCarNumber and mId>a.mId
)
--结束查询
drop table [SS_Main]
/*
mId mDate mIndex mCarNumber mLine
------------- ---------- ----------- ----------- --------------
YCZY101210220 2010-12-10 220 74213 保定-高碑店
YCGK101206161 2010-12-06 161 74791 南京-杭州-萧山
(2 行受影响)
select t.* from ss_main t where not exists(select 1 from ss_main where mCarNumber = t.mCarNumber and (mDate > t.mDate or (mDate = t.mDate and mIndex > t.mIndex))) order by t.mCarNumber
select t.* from ss_main t where not exists(select 1 from ss_main where mDate > t.mDate or (mDate = t.mDate and mIndex > t.mIndex)) order by t.mCarNumber
--try
select * from SS_Main a where not exists(
select 1 from SS_Main where mCarNumber=a.mCarNumber and mDate>a.mDate
)