SQL|同列多行数据，同一行显示
将同列多行数据，同一行显示
Oracle
wm_concat(column)
该函数将多列数据聚合成一行数据，以","号分隔，并且字段类型为clob类型。
示例：
统计客户的联系方式，并将多个联系方式一行显示。
1SQL> select * from test_table;
2
3CUSTNO TEL
4---------- ----------------------------------------
50001 13000000001
60001 18600000001
70002 13000000001
80002 13000000002
9
10SQL> SELECT custno,wm_concat(tel) AS tel FROM test_table GROUP BY custno;
11
12CUSTNO TEL
13---------- ----------------------------------------
140001 13000000001,18600000001
150002 13000000001,13000000002
16
17SQL> SELECT custno, dbms_lob.substr(wm_concat(tel), 100, 1) AS tel FROM test_table GROUP BY custno;
18
19CUSTNO TEL
20---------- ----------------------------------------
210001 13000000001,18600000001
220002 13000000001,13000000002
23
MySQL
1 GROUP_CONCAT([DISTINCT] expr [,expr ...]
2 [ORDER BY {unsigned_integer | col_name | expr}
3 [ASC | DESC] [,col_name ...]]
4 [SEPARATOR str_val])
示例：
1franky@localhost [test]>select * from test_table;
2+--------+-------------+
3| custno | tel |
4+--------+-------------+
5| 0001 | 13000000001 |
6| 0001 | 18600000001 |
7| 0002 | 13000000001 |
8| 0002 | 13000000002 |
9+--------+-------------+
10
11franky@localhost [test]>select custno,group_concat(tel) as tel from test_table group by custno;
12+--------+-------------------------+
13| custno | tel |
14+--------+-------------------------+
15| 0001 | 13000000001,18600000001 |
16| 0002 | 13000000001,13000000002 |
17+--------+-------------------------+
182 rows in set (0.00 sec)
19
20franky@localhost [test]>select custno
21 ,group_concat( DISTINCT tel ORDER BY tel DESC SEPARATOR ',') as tel
22 from test_table
23 group by custno;
24
25+--------+-------------------------+
26| custno | tel |
27+--------+-------------------------+
28| 0001 | 18600000001,13000000001 |
29| 0002 | 13000000002,13000000001 |
30+--------+-------------------------+
312 rows in set (0.00 sec)
32
Hive
concat_ws(string SEP, string A, string B…) SEP表示分隔符，返回string类型
collect_set(col) 排除重复元素，返回数组类型。
concat_ws(string SEP, array) SEP表示分隔符，返回string类型
示例：
10: jdbc:hive2://ip:port/> select * from test_table;
2+--------------------+-----------------+--+
3| test_table.custno | test_table.tel |
4+--------------------+-----------------+--+
5| 0001 | 13000000001 |
6| 0001 | 18600000001 |
7| 0002 | 13000000001 |
8| 0002 | 13000000002 |
9+--------------------+-----------------+--+
10
110: jdbc:hive2://ip:port/> select custno,collect_set(tel) as tel from test_table group by custno;
12+---------+--------------------------------+--+
13| custno | tel |
14+---------+--------------------------------+--+
15| 0001 | ["18600000001","13000000001"] |
16| 0002 | ["13000000002","13000000001"] |
17+---------+--------------------------------+--+
18
190: jdbc:hive2://ip:port/> select custno,concat_ws(',',collect_set(tel)) as tel from test_table group by custno;
20+---------+--------------------------+--+
21| custno | tel |
22+---------+--------------------------+--+
23| 0001 | 18600000001,13000000001 |
24| 0002 | 13000000002,13000000001 |
25+---------+--------------------------+--+
26
Postgresql
string_agg(expression, delimiter)
示例：
配置表ul_agg_test中记录了哪些表哪些字段需要脱敏，及脱敏规则。
1select * FROM cdb.ul_agg_test WHERE owner='odb' and table_name IN ('test1','test2','test3');
2
3 owner | table_name | column_name | method
4-------+------------+-------------+-----------------------------------
5 odb | test1 | bnknm | DECODE(GGGG,NULL,'','他行')
6 odb | test2 | payername | DECODE(GGGG,NULL,'','付款人姓名')
7 odb | test2 | payeename | DECODE(GGGG,NULL,'','付款人姓名')
8 odb | test3 | payername | DECODE(GGGG,NULL,'','付款人名称')
9 odb | test3 | midname | DECODE(GGGG,NULL,'','付款人名称')
10 odb | test3 | payeename | DECODE(GGGG,NULL,'','付款人名称')
11 (6 rows)
查询表哪些字段需要脱敏，并将字段拼接起来。
1SELECT table_name, string_agg(column_name, ',')
2 FROM CDB.ul_agg_test
3 WHERE owner = 'odb'
4 AND table_name IN ('test1', 'test2', 'test3')
5 GROUP BY table_name;
6
7 table_name | string_agg
8------------+-----------------------------
9 test1 | bnknm
10 test2 | payername,payeename
11 test3 | payername,midname,payeename
12(3 rows)
13