现在我有一个表如下:flag orderId
1 7
2 8
3 12
0 13
1 14
2 15
3 16
0 18
1 20
2 21flag列只有 0, 1, 2, 3 四个值现在要得到如下表:
flag1 flag2 flag3 flag0
7 8 12 13
14 15 16 18
20 21
1 7
2 8
3 12
0 13
1 14
2 15
3 16
0 18
1 20
2 21flag列只有 0, 1, 2, 3 四个值现在要得到如下表:
flag1 flag2 flag3 flag0
7 8 12 13
14 15 16 18
20 21
select (case when flag = 0 then orderid else null end) flag0,
(case when flag = 1 then orderid else null end) flag1,
(case when flag = 2 then orderid else null end) flag2,
(case when flag = 3 then orderid else null end) flag3
from tb
select
(case when flag = 0 then orderid else null end) as flag0,
(case when flag = 1 then orderid else null end) as flag1,
(case when flag = 2 then orderid else null end) as flag2,
(case when flag = 3 then orderid else null end) as flag3
from test
(case when flag=1 then orderid else '' end)flag1,
(case when flag=2 then orderid else '' end )flag2,
(case when flag=3 then orderid else '' end)flag3
from tb
create table tb(flag int,orderId int)
insert into tb values(1 ,7)
insert into tb values(2 ,8)
insert into tb values(3 ,12)
insert into tb values(0 ,13)
insert into tb values(1 ,14)
insert into tb values(2 ,15)
insert into tb values(3 ,16)
insert into tb values(0 ,18)
insert into tb values(1 ,20)
insert into tb values(2 ,21)
goselect max(case flag when 1 then orderId else null end) flag1,
max(case flag when 2 then orderId else null end) flag2,
max(case flag when 3 then orderId else null end) flag3,
max(case flag when 0 then orderId else null end) flag0
from
(
select t.* , px = (select count(1) from tb where flag = t.flag and orderId < t.orderId) + 1 from tb t
) m
group by pxdrop table tb/*
flag1 flag2 flag3 flag0
----------- ----------- ----------- -----------
7 8 12 13
14 15 16 18
20 21 NULL NULL(所影响的行数为 3 行)*/
select max(case flag when 1 then orderId else null end) flag1,
max(case flag when 2 then orderId else null end) flag2,
max(case flag when 3 then orderId else null end) flag3,
max(case flag when 0 then orderId else null end) flag0
from
(
select t.* , px = row_number() over(partition by flag order by orderId) from tb t
) m
group by px