sql 2000 对某一列分类

[daigazi]

假如有一列数据vehicle，范围是1~28，如下所示。
vehicle
1
2
3
4
...
28

我想根据这一列新增一列veh。即vehicle这一列的1~4为veh的1,5~18为veh的2,19~28为veh的3。
应该怎么写呢？？？

[Andy__Huang]

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

[Andy__Huang]

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

[daigazi]

引用 3 楼 hdhai9451 的回复:

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

不好意思，那如果是1~4和21~22是1呢？就是这种不是连续的。 另外，是新增一列，所以这个只是select出来，还要写入到元表里呢。小白，用的少，数据库比较大，怕写错了，半天出的结果不符合要求，就晕死了。

[daigazi]

引用 1 楼 hdhai9451 的回复:

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

问问题的时候贪图快了，没说清楚。 我的要求是这样的，1~4和21~22是1,23~28是2，其他是3，这样写不对哦 select top 10 Col007,case Col007 when （between 1 and 4 and between 21 and 22）then 1 when between 23 and 28 then 2 else 3 end as veh from boutlist201201

[daigazi]

引用 4 楼 daigazi 的回复:

[quote=引用 3 楼 hdhai9451 的回复:]

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

不好意思，那如果是1~4和21~22是1呢？就是这种不是连续的。 另外，是新增一列，所以这个只是select出来，还要写入到元表里呢。小白，用的少，数据库比较大，怕写错了，半天出的结果不符合要求，就晕死了。[/quote] 问问题的时候贪图快了，没说清楚。 我的要求是这样的，1~4和21~22是1,23~28是2，其他是3，这样写不对哦 select top 10 Col007,case Col007 when （between 1 and 4 and between 21 and 22）then 1 when between 23 and 28 then 2 else 3 end as veh from boutlist201201

[Andy__Huang]

同样是同有问题的,你加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

[daigazi]

引用 7 楼 hdhai9451 的回复:

同样是同有问题的,你加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

嗯，可以了。 就是没有新增一列到原表里去

[daigazi]

alter table boutlist201201 add veh int update boutlist201201 set veh=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 说veh无效

[Andy__Huang]

这是一个计算列,完全不需要加一列到原表去.这是设计的问题