--测试数据
create table #tbl(License varchar(10),CreateDate datetime,TotalMileage int)
insert #tbl select '京A13167', '2005-5-9', 200
insert #tbl select '京A13167', '2005-5-19', 300
insert #tbl select '京A13167', '2006-5-9', 2000
insert #tbl select '京A13168', '2005-5-9', 100
insert #tbl select '京A13168', '2005-5-12', 190--查询
select a.license,a.totalmileage-b.totalmileage as mileage from #tbl a,#tbl b,
(select license,min(createdate) as 'startdate',max(createdate) as 'enddate'
from #tbl where createdate between '2005-5-9' and '2006-5-19'
group by license) c
where a.license=b.license
and a.license=b.license
and a.createdate=c.enddate
and b.createdate=c.startdate
/*结果
license mileage
---------- -----------
京A13167 1800
京A13168 90(所影响的行数为 2 行)
create table #tbl(License varchar(10),CreateDate datetime,TotalMileage int)
insert #tbl select '京A13167', '2005-5-9', 200
insert #tbl select '京A13167', '2005-5-19', 300
insert #tbl select '京A13167', '2006-5-9', 2000
insert #tbl select '京A13168', '2005-5-9', 100
insert #tbl select '京A13168', '2005-5-12', 190--查询
select a.license,a.totalmileage-b.totalmileage as mileage from #tbl a,#tbl b,
(select license,min(createdate) as 'startdate',max(createdate) as 'enddate'
from #tbl where createdate between '2005-5-9' and '2006-5-19'
group by license) c
where a.license=b.license
and a.license=b.license
and a.createdate=c.enddate
and b.createdate=c.startdate
/*结果
license mileage
---------- -----------
京A13167 1800
京A13168 90(所影响的行数为 2 行)
from table
where CreateDate between '2005-5-9' and '2006-5-19'
group by License
insert @T
select '京A13167','2005-5-9',200
union select '京A13167','2005-5-19' ,300
union select '京A13167' ,'2006-5-9',2000
union select '京A13168','2005-5-9',100
union select '京A13168','2005-5-12',190
select License,max(TotalMileage) - min(TotalMileage)
from @t
where CreateDate between '2005-5-9' and '2006-5-19'
group by License
(所影响的行数为 5 行)License
------------------------------ ---------------------
京A13167 1800
京A13168 90(所影响的行数为 2 行)