有没有一条SQL语句可以完成column一对多

显示全部楼层 · 2021-10-12 20:11:36

ab
ab
－－－－》－－
1A
1A
2A
2
3B
3B
4C
4C
5C
5
11 D
7D
8E
11
7D
8E
9K
9K

要求：b列去重，b对a为1比n，按b列排序（b列重复为空，且紧接着对应的值［如第二列第二行的空值紧接A下面而不是跑到其他行］），每组b列对应的a列内部升序（如D对应7，11，7与11在D组内部升序排列）
错误示范：
select distinct c.b,d.a from tab c right outer join tab d on c.b=d.b order by 1;

千问 · 2021-10-12 20:11:36

排序之后用分析函数搞一下就好了

千问 · 2021-10-12 20:11:36

with t as (select 1 id,'A' nm from dual union all
select 2,'A'from dual union all
select 3 , 'B' from dual union all
select 4,'C' from dual union all
select 5,'C'from dual union all
select 11, 'D' from dual union all select 8 , 'E' from dual union all select 7 , 'D' from dual union all select 9 , 'K'from duaL),T1 AS(SELECT ID,NM FROM T ORDER BY NM,ID),T2 AS(SELECT ID,NM,LAG(NM)OVER(ORDER BY rownum) LG FROM T1)SELECT ID,DECODE(NM,LG,'',NM) FROM T2复制代码

千问 · 2021-10-12 20:11:36

SQL> select * from test;
A B
---------- --
1 A
2 A
3 B
4 C
5 C
11 D
8 E
7 D
9 K
9 rows selected.
SQL> select a,(case when a not in (select min(a) from test group by b) then '' else b end) b from test;
A B
---------- --
1 A
2
3 B
4 C
5
11
8 E
7 D
9 K
9 rows selected.

千问 · 2021-10-12 20:11:36

645930266 发表于 2015-8-12 14:17
SQL> select * from test;
A B
厉害！我用rownum（）整出来但语句很复杂，喜欢你这条清晰的sql

千问 · 2021-10-12 20:11:36

2009532140 发表于 2015-8-12 14:10
大神！

千问 · 2021-10-12 20:11:36

方法很多
SQL> with t as (select 1 id,'A' nm from dual union all
2select 2,'A'from dual union all
3select 3 , 'B' from dual union all
4select 4,'C' from dual union all
5select 5,'C'from dual union all
6select 11, 'D' from dual union all
7select 8 , 'E' from dual union all
8select 7 , 'D' from dual union all
9select 9 , 'K'from duaL)
10select id,
11 decode(row_number() over(partition by nm order by id), 1, nm, null) nm
12from t
13/
ID NM
---------- --
1 A
2
3 B
4 C
5
7 D
11
8 E
9 K
9 rows selected
SQL>

千问 · 2021-10-12 20:11:36

bell6248 发表于 2015-8-12 15:37
方法很多
SQL> with t as (select 1 id,'A' nm from dual union all
你这个跟q群大神给我的几乎一模一样

千问 · 2021-10-12 20:11:36

break on b

千问 · 2021-10-12 20:11:36

create table test_2(id integer,name text)
insert into test_2 values(1,'A');
insert into test_2 values(2,'A');
insert into test_2 values(3,'B');
insert into test_2 values(4,'C');
insert into test_2 values(5,'C');
insert into test_2 values(11,'D');
insert into test_2 values(8,'E');
insert into test_2 values(7,'D');
insert into test_2 values(9,'K');
SELECTID, CASE WHEN (ROW_NUMBER()OVER(PARTITION BY NAME ORDERBY ID))=1 THEN NAME

ELSE NULL END AS NAME FROM TEST_2
1;"A"
2;""
3;"B"
4;"C"
5;""
7;"D"
11;""
8;"E"
9;"K"