比如,给定12、30,
可以生成一列(可以放在临时表里#temp):
Numb
12
13
14
15
16
.........
30并用SQL语句(不用游标)分成两列(从小到大的顺序)显示:Numb1 Numb2
12 13
14 15
16 17
............
28 29
30 麻烦大家给看看,谢谢。
可以生成一列(可以放在临时表里#temp):
Numb
12
13
14
15
16
.........
30并用SQL语句(不用游标)分成两列(从小到大的顺序)显示:Numb1 Numb2
12 13
14 15
16 17
............
28 29
30 麻烦大家给看看,谢谢。
declare @num2 as int
set @num1 = 12
set @num2 = 30select @num1 + m.id numb from # m where @num1 + m.id <= @num2drop table #/*
numb
-----------
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30(所影响的行数为 19 行)
*/
declare @num2 as int
set @num1 = 12
set @num2 = 30select @num1 + m.id numb from # m where @num1 + m.id <= @num2
/*
numb
-----------
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30(所影响的行数为 19 行)
*/select
max(case num2 when 0 then num end) num1,
max(case num2 when 1 then num end) num2
from
(
select (@num1 + m.id)/2 num1 , (@num1 + m.id)%2 num2 , @num1 + m.id num from # m where @num1 + m.id <= @num2
) t
group by num1
order by num1
/*
num1 num2
----------- -----------
12 13
14 15
16 17
18 19
20 21
22 23
24 25
26 27
28 29
30 NULL(所影响的行数为 10 行)
*/drop table #
go
create table tb(Numb int)
insert tb select 12
insert tb select 13
insert tb select 14
insert tb select 15
insert tb select 16
insert tb select 17
insert tb select 18
insert tb select 19
select A=case when numb%2=0 then numb end ,
B=case when numb%2!=0 then numb end
from tb
/*A B
----------- -----------
12 NULL
NULL 13
14 NULL
NULL 15
16 NULL
NULL 17
18 NULL
NULL 19
*/
比如,可能是下面一列数:
12
14
16
17
18
19
20
22
25
27
.........
但是要顺序排成两列,如下:
Numb1 Numb2
12 14
16 17
18 19
20 22
25 27
..........
大家看有办法做到吗? 多谢~~!
create table tb(id int)
insert into tb values(12)
insert into tb values(14)
insert into tb values(16)
insert into tb values(17)
insert into tb values(18)
insert into tb values(19)
insert into tb values(20)
insert into tb values(22)
insert into tb values(25)
insert into tb values(27)
goselect
max(case (px - 1) % 2 when 0 then id end) num1,
max(case (px - 1) % 2 when 1 then id end) num2
from
(
select * , px = (select count(1) from tb where id < t.id) + 1 from tb t
) m
group by (px - 1) / 2drop table tb/*
num1 num2
----------- -----------
12 14
16 17
18 19
20 22
25 27(所影响的行数为 5 行)
*/