经过一些朋友的帮助,我的这个sql已经可以查询出来了,现在我有个问题,就是在红色字体处 我要判断,就是当c.maxmob*2<10的时候,c.maxmob就等于10,否则大于10,我试着用 case when c.maxmob*<10 then '10' end的方式来写 却报错了,请教高手如何写呢?
select ROW_NUMBER() over (ORDER BY a.gpstime desc) sp,'油量测试' tname,
Convert(decimal(10,2),(a.mil-b.mil)/1000.00) mils,
Convert(decimal(10,2),(a.mil-b.mil)/100000.00*10.00) consumes,
(c.maxmob+'-'+c.minmob) mobs,a.gpstime gpstimes
from (select distinct mil,CONVERT(nvarchar(100), gpstime, 23) gpstime from [gpsdatahistory].[dbo].db_9310228028
where id in(select max(id) from [gpsdatahistory].[dbo].db_9310228028
where gpstime between CONVERT(nvarchar(100), '2011-5-10 00:00:00', 23) and CONVERT(nvarchar(100), '2011-5-26 00:00:00', 23)
group by CONVERT(nvarchar(100), gpstime, 23))) a,(select distinct mil,CONVERT(nvarchar(100), gpstime, 23) gpstime
from [gpsdatahistory].[dbo].db_9310228028 where id in( select min(id) from [gpsdatahistory].[dbo].db_9310228028
where gpstime between CONVERT(nvarchar(100), '2011-5-10 00:00:00', 23) and CONVERT(nvarchar(100), '2011-5-26 00:00:00', 23)
group by CONVERT(nvarchar(100), gpstime, 23))) b,(select convert(nvarchar(20),gpstime,23) gpstime, max(mob) maxmob,min(mob) minmob
from [gpsdatahistory].[dbo].db_9310228028
where gpstime between CONVERT(nvarchar(100), '2011-5-10 00:00:00', 23) and CONVERT(nvarchar(100), '2011-5-26 00:00:00', 23)
group by convert(nvarchar(20),gpstime,23)) cwhere a.gpstime=b.gpstime and b.gpstime=c.gpstime
select ROW_NUMBER() over (ORDER BY a.gpstime desc) sp,'油量测试' tname,
Convert(decimal(10,2),(a.mil-b.mil)/1000.00) mils,
Convert(decimal(10,2),(a.mil-b.mil)/100000.00*10.00) consumes,
(c.maxmob+'-'+c.minmob) mobs,a.gpstime gpstimes
from (select distinct mil,CONVERT(nvarchar(100), gpstime, 23) gpstime from [gpsdatahistory].[dbo].db_9310228028
where id in(select max(id) from [gpsdatahistory].[dbo].db_9310228028
where gpstime between CONVERT(nvarchar(100), '2011-5-10 00:00:00', 23) and CONVERT(nvarchar(100), '2011-5-26 00:00:00', 23)
group by CONVERT(nvarchar(100), gpstime, 23))) a,(select distinct mil,CONVERT(nvarchar(100), gpstime, 23) gpstime
from [gpsdatahistory].[dbo].db_9310228028 where id in( select min(id) from [gpsdatahistory].[dbo].db_9310228028
where gpstime between CONVERT(nvarchar(100), '2011-5-10 00:00:00', 23) and CONVERT(nvarchar(100), '2011-5-26 00:00:00', 23)
group by CONVERT(nvarchar(100), gpstime, 23))) b,(select convert(nvarchar(20),gpstime,23) gpstime, max(mob) maxmob,min(mob) minmob
from [gpsdatahistory].[dbo].db_9310228028
where gpstime between CONVERT(nvarchar(100), '2011-5-10 00:00:00', 23) and CONVERT(nvarchar(100), '2011-5-26 00:00:00', 23)
group by convert(nvarchar(20),gpstime,23)) cwhere a.gpstime=b.gpstime and b.gpstime=c.gpstime
消息 245,级别 16,状态 1,第 1 行
在将 nvarchar 值 '9.7' 转换成数据类型 int 时失败。
这个如何转成int啊?
((case when c.maxmob*<10 then '10' else ltrim(c.maxmob) end)+'-'+c.minmob) mobs
((case when Convert(decimal(10,2),c.maxmob)*2<'10' then '<10' when Convert(decimal(10,2),c.maxmob)*2>='100' then '100' else Convert(decimal(10,2),c.maxmob)*2+10 end)+'-'+c.minmob) mobs
when Convert(decimal(10,2),c.maxmob)*2<'10' then '<10'
when Convert(decimal(10,2),c.maxmob)*2>='100' then '100'
else ltrim(Convert(decimal(10,2),c.maxmob)*2+10) end)+'-'+ltrim(c.minmob)) mobs