假如有一列数据vehicle，范围是1~28，如下所示。
vehicle
1
2
3
4
...
28
我想根据这一列新增一列veh。即vehicle这一列的1~4为veh的1,5~18为veh的2,19~28为veh的3。
应该怎么写呢？？？
------解决方案--------------------
select vehicle,case vehicle when between 1 and 4 then 1
when between 5 and 18 then 2
when between 19 and 28 then 3 else 4 end as veh
from tb
------解决方案--------------------
create table #tab(vehicle int)
declare @i int
set @i=1
while @i<=28
begin
insert into #tab values(@i)
set @i=@i+1
end
alter table #tab add vehi int
update #tab set vehi=case when vehicle between 1 and 4 then 1
when vehicle between 5 and 18 then 2
when vehicle between 19 and 28 then 3 end
go
select * from #tab
drop table #tab
------解决方案--------------------
select vehicle,case when vehicle between 1 and 4 then 1
when vehicle between 5 and 18 then 2
when vehicle between 19 and 28 then 3 else 4 end as veh
from tb
------解决方案--------------------
同样是同有问题的,你加top 10 也是可以的
select top 10 Col007,case when Col007 between 1 and 4 or Col007 in(21,22）then 1
when Col007 between 23 and 28 then 2
else 3 end as veh
from boutlist201201
------解决方案--------------------