22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT
调度单号,车号,行驶日期 ,序号 ,行驶线路
FROM (Select 调度单号,车号,行驶日期 ,序号 ,行驶线路,ROW_NUMBER()OVER(PARTITION BY 车号 ORDER BY 行驶日期 DESC) AS row1,ROW_NUMBER()OVER(PARTITION BY 车号 ORDER
BY 行驶日期 DESC) AS row2 from 表SS_Main
)t
WHERE row1=1
SELECT
调度单号,车号,行驶日期 ,序号 ,行驶线路
FROM (Select 调度单号,车号,行驶日期 ,序号 ,行驶线路
,ROW_NUMBER()OVER(PARTITION BY 车号 ORDER BY 行驶日期 DESC) AS row1,ROW_NUMBER()OVER(PARTITION BY 车号 ORDER
BY 行驶日期 asc) AS row2 from 表SS_Main
)t
WHERE row2=1
create table ss_main(车号 varchar(10),行驶日期 datetime,序号 int)
insert into ss_main select '1234','2011-12-14',1
insert into ss_main select '1234','2011-12-14',3
insert into ss_main select '1234','2011-12-15',1
insert into ss_main select '1234','2011-12-15',2
insert into ss_main select '1234','2011-12-15',3
insert into ss_main select '1235','2011-12-16',7
insert into ss_main select '1235','2011-12-16',8
insert into ss_main select '1235','2011-12-15',15
go
select * from SS_Main a
where not exists(select 1 from ss_main where 车号=a.车号 and (行驶日期>a.行驶日期 or 行驶日期=a.行驶日期 and 序号>a.序号))
/*
车号 行驶日期 序号
---------- ----------------------- -----------
1234 2011-12-15 00:00:00.000 3
1235 2011-12-16 00:00:00.000 8
(2 行受影响)
*/
go
select * from SS_Main a
where not exists(select 1 from ss_main where 车号=a.车号 and 行驶日期>a.行驶日期 or 行驶日期=a.行驶日期 and 序号>a.序号)
--2005+
select
distinct b.*
from
tb a
cross apply
(select top 1 * from tb where 车号=a.车号 order by 行驶日期 desc)b
--TB是指你查出的结果集