select
a=max(case when a%3 =1 then a end),
b=max(case when a%3 =2 then a end),
c=max(case when a%3 =0 then a end)
from
t
group by (a-1)/3
a=max(case when a%3 =1 then a end),
b=max(case when a%3 =2 then a end),
c=max(case when a%3 =0 then a end)
from
t
group by (a-1)/3
as
(select 1 as a
union all
select a+1 from T where a+1<10)
select
a=max(case when (a-1)/3 =0 then a end),
b=max(case when (a-1)/3 =1 then a end),
c=max(case when (a-1)/3 =2 then a end)
from
t
group by a%3
order by a
where a.a<=c
a b c
----------- ----------- -----------
1 4 7
2 5 8
3 6 9(3 個資料列受到影響)
*/
if object_id('[tb]') is not null drop table [tb]
create table [tb] (a int)
insert into [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9;with t as
(
select a, ntile(3) over(order by a) as b from tb
)
select t1.a as a, t2.a as b, t3.a as c
from (select *, row_number() over(order by a) as rn from t where b=1) t1
left join (select *, row_number() over(order by a) as rn from t where b=2) t2 on t1.rn=t2.rn
left join (select *, row_number() over(order by a) as rn from t where b=3) t3 on t2.rn=t3.rn/*
a b c
----------- ----------- -----------
1 4 7
2 5 8
3 6 9(3 row(s) affected)
*/
drop table tb
create table [tb] (a int)
insert into [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9select a,b=a+c,c=a+2*c from tb a,(select c=(count(*)+2)/3 from tb ) b where a.a<=c
/*
a b c
----------- ----------- -----------
1 4 7
2 5 8
3 6 9
*/
你牛X,a要是不连续呢?
-----------------
修改表,加一列ID
主要是2005我不会。
dobear_0922我不知道结果会是什么,因为我不懂row_number(),
Herb2的正如dobear_0922所说
不过仍要谢谢大家~分数就给以上3位了~散分~只是为什么没有人会想用这个
select a=
case when a<=3 then a end a,
when a>3 and a<=6 then a end b,
when a>6 and a<=9 then a end c
from table
大家请用鼠标拖动我问题内容,最下面那句话~~~
问题在我醒来的时候就解决了,呵呵~~~