34,576
社区成员
发帖
与我相关
我的任务
分享
declare @distance table(carmark varchar(50),time1 datetime,time2 datetime, licheng1 real,licheng2 real,distance real)
insert into @distance values('S09238','2008-03-01 00:01:15','2008-03-27 16:25:04', 9057.1, 13108.4, 4051.3)
insert into @distance values('S19271','2008-03-01 00:01:15','2008-03-27 16:25:04', 9116.5, 12103.7, 2987.2 )
insert into @distance values('S22126','2008-03-01 00:01:16','2008-03-27 16:25:07', 15109.6, 20704.6, 5595)
insert into @distance values('S30737','2008-03-01 00:01:15','2008-03-27 16:25:04', 7663.6, 9454.1, 1790.5)
declare @carinfo table(carmark varchar(50),oil real,[money] real)
insert into @carinfo values('S09238', 2.5 , 8.0)
insert into @carinfo values('S19271', 1.5 , 7.0)
insert into @carinfo values('S22126', 1.6 , 7.5)
insert into @carinfo values('S30737', 0.8 , 7.8)
declare @Maintain table(carmark varchar(50),money1 real,add_date datetime)
insert into @Maintain values('S19271', 4500, '2008-03-15 00:01:15')
insert into @Maintain values('S22126', 5600, '2008-03-23 00:01:15')
insert into @Maintain values('S30737', 3457, '2008-03-24 00:01:15')
insert into @Maintain values('S09238', 2596, '2008-02-12 00:01:15')
insert into @Maintain values('S09238', 2590, '2008-03-01 00:01:15')
insert into @Maintain values('S09238', 1320, '2008-03-20 00:01:15')
select distance.carmark,distance.[month],distance.distance,money4.money4,money2.money2,round(money4.money4+money2.money2,2) as money3 from
(select carmark,month(time1)as [month],distance from @distance) as distance,
(select c.carmark as carmark,(d.distance/c.oil)*c.[money] as money2 from @carinfo as c,@distance as d where d.carmark=c.carmark) as money2,
(select carmark,month(add_date) as [month],sum(money1) as money4 from @Maintain where month(add_date)=3 group by carmark,month(add_date)) as money4
where distance.carmark=money2.carmark and distance.carmark=money4.carmark
/*
S09238 3 4051.3 3910 12964.16 16874.16
S19271 3 2987.2 4500 13940.27 18440.27
S22126 3 5595 5600 26226.56 31826.56
S30737 3 1790.5 3457 17457.38 20914.38
*/
select * from GetDistance ('2008-3-1',convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120))
--输入参数
declare @date varchar(7)
set @date='2008-3'
--查询语句
select @date+'-1', dateadd(month,1,@date+'-1')-1
select a.carmark,[month]=month(@date+'-1'),distance=licheng2-licheng1,
money4=sum(money1),money2=((licheng2-licheng1)/oil)*[money],money3=sum(money1)+((licheng2-licheng1)/oil)*[money]
from GetDistance (@date+'-1',dateadd(month,1,@date+'-1')-1) a inner join @CarInfo b on a.carmark=b.carmark
inner join @Maintain c on a.carmark=c.carmark
group by a.carmark,a.time1,a.licheng2,a.licheng1,oil,[money]
--输入参数
declare @date varchar(7)
set @date='2008-3'
--查询语句
select @date+'-1', dateadd(month,1,@date+'-1')-1
select a.carmark,[month]=month(@date+'-1'),distance=licheng2-licheng1,
money4=sum(money1),money2=((licheng2-licheng1)/oil)*[money],money3=sum(money1)+((licheng2-licheng1)/oil)*[money]
from GetDistance (@date+'-1',dateadd(month,1,@date+'-1')-1) a inner join @CarInfo b on a.carmark=b.carmark
inner join @Maintain c on a.carmark=c.carmark
group by a.carmark,a.time1,a.licheng2,a.licheng1,oil,[money]