DAY 17
4

## 程式設計師之入門基礎必備知識(17)-SQL進階變化

SYS_CONNECT_BY_PATH 合併多行字串

``````ID    money
35      100
35      200
35      300
``````

``````ID        money
35        100,200,300
``````

``````SELECT id , money,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY money) AS NO1
FROM my_table
ORDER BY id;
``````

``````SELECT id, money, NO1, level
FROM
(SELECT id, money,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY money) AS NO1
FROM my_table
ORDER BY id ) NO2
CONNECT BY id = PRIOR id
AND NO1 - 1 = PRIOR NO1
``````

``````SELECT NO2.id, NO2.money, SYS_CONNECT_BY_PATH(NO2.money, ',') AS money_list
FROM (SELECT id, money,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY money) AS NO1
FROM my_table
ORDER BY id ) NO2
CONNECT BY NO2.id = PRIOR NO2.id
AND NO2.NO1 - 1 = PRIOR NO2.NO1
``````

``````SELECT NO2.id,  SUBSTR(MAX(SYS_CONNECT_BY_PATH(NO2.money,',')),2) AS money_list
FROM
(SELECT id, money,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY money) AS NO1
FROM my_table
ORDER BY id ) NO2
CONNECT BY NO2.id = PRIOR NO2.id
AND NO2.NO1 - 1 = PRIOR NO2.NO1
GROUP BY NO2.id
order by NO2.id
``````

http://ithelp.ithome.com.tw/ironman6/player/ianianwu/other/1

### 1 則留言

0

iT邦大師 1 級 ‧ 2013-10-02 20:57:31

``````&lt;pre class="c" name="code">CREATE TABLE ithelp1002 (
id CHAR(2) NOT NULL,
money INT NOT NULL
);

INSERT INTO ithelp1002(id, money) VALUES
('35', 200), ('35', 100), ('35', 300),
('37', 410), ('37', 250);

SELECT *
FROM ithelp1002;

+----+-------+
| id | money |
+----+-------+
| 35 |   200 |
| 35 |   100 |
| 35 |   300 |
| 37 |   410 |
| 37 |   250 |
+----+-------+

SELECT id, GROUP_CONCAT(money ORDER BY money ASC SEPARATOR ',') AS moneys
FROM ithelp1002
GROUP BY id;

+----+-------------+
| id | moneys      |
+----+-------------+
| 35 | 100,200,300 |
| 37 | 250,410     |
+----+-------------+
2 rows in set (0.00 sec)
``````
ianianwu iT邦研究生 5 級 ‧ 2013-10-02 21:58:40 檢舉

]真的每個資料庫系統用的方法都不同阿，MYSQL在這邊好簡單阿