在sqlserver中求一sql:(急)

[快跑蜗牛哥]

已知：select * from GetDistance ('2008-03-1' ,'2008-03-31 ')
可得到如下结果,(其中distance=licheng2-licheng1)
carmark time1 time2 licheng1 licheng2 distance
S09238 2008-03-01 00:01:15 2008-03-27 16:25:04 9057.1 13108.4 4051.3
S19271 2008-03-01 00:01:15 2008-03-27 16:25:04 9116.5 12103.7 2987.2
S22126 2008-03-01 00:01:16 2008-03-27 16:25:07 15109.6 20704.6 5595
S30737 2008-03-01 00:01:15 2008-03-27 16:25:04 7663.6 9454.1 1790.5
...
另一表(CarInfo)中有如下数据：
carmark oil money
S09238 2.5 8.0
S19271 1.5 7.0
S22126 1.6 7.5
S30737 0.8 7.8
...

还有一表(Maintain)中有如下数据：
carmark money1 add_date
S09238 2596 2008-02-12 00:01:15
S09238 2590 2008-03-01 00:01:15
S09238 1320 2008-03-20 00:01:15
S19271 4500 2008-03-15 00:01:15
S22126 5600 2008-03-23 00:01:15
S30737 3457 2008-03-24 00:01:15
...
现要求，求一sql语句。当前月份是2008-3时可得到如下数据(其中money2=(distance/oil)*money
，money3=money4+money2，money4等于<属于当前月份carmark相同时，money1的和>)
carmark month distance money4 money2 money3
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 790.5 3457 17457.38 20914.38
...

[newtown555]

--输入参数
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]

[newtown555]

--输入参数
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]

[快跑蜗牛哥]

非常感觉你们的帮忙
select * from GetDistance ('2008-3-1',dateadd(month,1,'2008-3-1')-1)
// 'dateadd' 附近有语法错误。
select * from GetDistance ('2008-3-1',convert(char(19),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120))
//在关键字 'convert' 附近有语法错误。
select * from GetDistance ('2008-3-1','2008-3-31')
//正確~~
请兄弟们再看下，是哪里错误啊~~
感激不尽

[hery2002]

修改一下转换类型,换成 varchar(20)试试.

select * from GetDistance ('2008-3-1',convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120))  

[flairsky]

dateadd(month,1,'2008-3-1') 你这里还是datetime格式，-1接后面怎么理解？

[newtown555]

datetime格式本质也是数字，直接-1表示减一天

[快跑蜗牛哥]

to: newtown555 不行啊~~报错啊
思路是我要求的~~
sql语句运行出错~~

[快跑蜗牛哥]

忘记说了，其中GetDistance是自定义函数
CREATE FUNCTION GetDistance(@Time1 varchar(20),@Time2 Varchar(20)) RETURNS table AS return( select CarMark,Time1,Time2,LiCheng1,LiCheng2,Distance=LiCheng2-LiCheng1 from ( select CarMark,time1,time2 ,LiCheng1=(select top 1 CarLiCheng from XingShiLiCheng b where topDate=a.time1 and b.CarMark=a.CarMark) ,LiCheng2=(select top 1 CarLiCheng from XingShiLiCheng c where topDate=a.time2 and c.CarMark=a.CarMark) from (select CarMark,time1=min(topDate),time2=max(topDate) from [XingShiLiCheng] where topDate between @time1 and @Time2 group by CarMark) a ) t )

[快跑蜗牛哥]

select * from GetDistance ('2008-3-1',dateadd(month,1,'2008-3-1')-1)
出錯~~

[flairsky]

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
*/

[flairsky]

至于你distance怎么出来的，我就不管了阿~

[让你望见影子的墙]

select * from GetDistance ('2008-3-1',dateadd(month,1,'2008-3-1')-1)
出錯~~
出错的原因可能在与GetDistance的两个参数都是字符型的，而dateadd(month,1,'2008-3-1')-1产生的结果不是字符型的。
lz看看是不是这个原因

[newtown555]

应该是类型错误，换成GetDistance ('2008-3-1',convert(char(19),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120)) 试一下

[快跑蜗牛哥]

select * from GetDistance ('2008-3-1',convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120))
一樣~~
在关键字 'convert' 附近有语法错误。

[让你望见影子的墙]

select '2008-3-1',convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120)
这个语句是没有问题的，结果是2008-3-1 2008-03-31 23:59:59

[快跑蜗牛哥]

不用自定函数，就可以，
多谢兄弟们的鼎立相助，再次感谢，
尤其是newtown555 中午再结帖，看还有没更好的解决方法

[快跑蜗牛哥]

to:HEROWANG
单独运行
select '2008-3-1',convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120)
是没问题,
跟自定义函数结合就有问题，
可能是函数的问题吧，
谢谢你的关注~~

[viva369]

declare @d1 varchar(20)
set @d1 = convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2007-3-1')),120)
select * from GetDistance('2007-3-23',@d1)

[kaikai_kk]

看看

[viva369]

函数参数中使用函数,这有点像函数指针了
存储过程也是这样。