请问列1如何转换得到列2?
谢谢
列1 列2
0 [0,5)
1 [0,5)
4 [0,5)
5 [5,10)
6 [5,10)
7 [5,10)
8 [5,10)
9 [5,10)
15 [10,20)
16 [10,20)
17 [10,20)
18 [10,20)
19 [10,20)
谢谢
列1 列2
0 [0,5)
1 [0,5)
4 [0,5)
5 [5,10)
6 [5,10)
7 [5,10)
8 [5,10)
9 [5,10)
15 [10,20)
16 [10,20)
17 [10,20)
18 [10,20)
19 [10,20)
-- Author :SQL77(只为思齐老)
-- Date :2010-03-24 22:05:34
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#a
if object_id('tempdb.dbo.#a') is not null drop table #a
go
create table #a([列1] int,[列2] varchar(7))
insert #a
select 0,'[0,5)' union all
select 1,'[0,5)' union all
select 4,'[0,5)' union all
select 5,'[5,10)' union all
select 6,'[5,10)' union all
select 7,'[5,10)' union all
select 8,'[5,10)' union all
select 9,'[5,10)' union all
select 15,'[10,20)' union all
select 16,'[10,20)' union all
select 17,'[10,20)' union all
select 18,'[10,20)' union all
select 19,'[10,20)'
--------------开始查询--------------------------select 列1,
CASE WHEN 列1>=0 and 列1<5 then '[0-5)' else
CASE WHEN 列1>=5 and 列1<10 then '[5-10)' else
CASE WHEN 列1>=10 and 列1<20 then '[10-20)' end end
end
from #a
----------------结果----------------------------
/* (所影响的行数为 13 行)列1
----------- -------
0 [0-5)
1 [0-5)
4 [0-5)
5 [5-10)
6 [5-10)
7 [5-10)
8 [5-10)
9 [5-10)
15 [10-20)
16 [10-20)
17 [10-20)
18 [10-20)
19 [10-20)(所影响的行数为 13 行)
*/
go
create table #a([列1] int,[列2] varchar(7))
insert #a
select 0,'[0,5)' union all
select 1,'[0,5)' union all
select 4,'[0,5)' union all
select 5,'[5,10)' union all
select 6,'[5,10)' union all
select 7,'[5,10)' union all
select 8,'[5,10)' union all
select 9,'[5,10)' union all
select 15,'[10,20)' union all
select 16,'[10,20)' union all
select 17,'[10,20)' union all
select 18,'[10,20)' union all
select 19,'[10,20)'
--------------开始查询--------------------------select 列1,'['+ltrim(((列1+5)/10)*5)+','+ltrim(((列1/5)+1)*5)+')' from #a列1
----------- ---------------------------
0 [0,5)
1 [0,5)
4 [0,5)
5 [5,10)
6 [5,10)
7 [5,10)
8 [5,10)
9 [5,10)
15 [10,20)
16 [10,20)
17 [10,20)
18 [10,20)
19 [10,20)(13 行受影响)