• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

在sqlserver中求一sql:(急)

快跑蜗牛哥 2008-03-27 06:23:29

已知: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
...
...全文
179 点赞 收藏 22
写回复
22 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
viva369 2008-03-29
函数参数中使用函数,这有点像函数指针了
存储过程也是这样。
回复
kaikai_kk 2008-03-29
看看
回复
viva369 2008-03-29
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)
回复
快跑蜗牛哥 2008-03-29
to:HEROWANG
单独运行
select '2008-3-1',convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120)
是没问题,
跟自定义函数结合就有问题,
可能是函数的问题吧,
谢谢你的关注~~
回复
快跑蜗牛哥 2008-03-29
不用自定函数,就可以,
多谢兄弟们的鼎立相助,再次感谢,
尤其是newtown555
中午再结帖,看还有没更好的解决方法
回复
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

回复
快跑蜗牛哥 2008-03-29
select * from GetDistance ('2008-3-1',convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120))
一樣~~
在关键字 'convert' 附近有语法错误。
回复
newtown555 2008-03-28
应该是类型错误,换成GetDistance ('2008-3-1',convert(char(19),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120)) 试一下
回复
select * from GetDistance ('2008-3-1',dateadd(month,1,'2008-3-1')-1)
出錯~~
出错的原因可能在与GetDistance的两个参数都是字符型的,而dateadd(month,1,'2008-3-1')-1产生的结果不是字符型的。
lz看看是不是这个原因
回复
flairsky 2008-03-28
至于你distance怎么出来的,我就不管了阿~

回复
flairsky 2008-03-28
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
*/
回复
快跑蜗牛哥 2008-03-28
select * from GetDistance ('2008-3-1',dateadd(month,1,'2008-3-1')-1)
出錯~~
回复
快跑蜗牛哥 2008-03-28
忘记说了,其中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 )
回复
快跑蜗牛哥 2008-03-28
to: newtown555 不行啊~~报错啊
思路是我要求的~~
sql语句运行出错~~
回复
newtown555 2008-03-28
datetime格式本质也是数字,直接-1表示减一天
回复
flairsky 2008-03-28
dateadd(month,1,'2008-3-1') 你这里还是datetime格式,-1接后面怎么理解?
回复
hery2002 2008-03-28
修改一下转换类型,换成 varchar(20)试试.

select * from GetDistance ('2008-3-1',convert(varchar(20),dateadd(second,-1,dateadd(month,1,'2008-3-1')),120))
回复
快跑蜗牛哥 2008-03-28
非常感觉你们的帮忙
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')
//正確~~
请兄弟们再看下,是哪里错误啊~~
感激不尽
回复
newtown555 2008-03-27
--输入参数
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 2008-03-27
--输入参数
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]
回复
加载更多回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-27 06:23
社区公告
暂无公告