Let's say we have a table team_person as below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
+======+===========+
| team | person |
+======+===========+
| A | John |
+------+-----------+
| B | Smith |
+------+-----------+
| A | Walter |
+------+-----------+
| A | Louis |
+------+-----------+
| C | Elizabeth |
+------+-----------+
| B | Wayne |
+------+-----------+
CREATE TABLE team_person AS SELECT 'A' team, 'John' person
UNION ALL SELECT 'B' team, 'Smith' person
UNION ALL SELECT 'A' team, 'Walter' person
UNION ALL SELECT 'A' team, 'Louis' person
UNION ALL SELECT 'C' team, 'Elizabeth' person
UNION ALL SELECT 'B' team, 'Wayne' person;
|
cs |
To select the table team_person with additional row_number column, either
1
2
|
SELECT @row_no := @row_no+1 AS row_number, team, person
FROM team_person, (SELECT @row_no := 0) t;
|
cs |
* rownum 초기화 : (SELECT @row_no := 0) t;
OR
1
2
3
|
SET @row_no := 0;
SELECT @row_no := @row_no + 1 AS row_number, team, person
FROM team_person;
|
cs |
will output the result below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
+============+======+===========+
| row_number | team | person |
+============+======+===========+
| 1 | A | John |
+------------+------+-----------+
| 2 | B | Smith |
+------------+------+-----------+
| 3 | A | Walter |
+------------+------+-----------+
| 4 | A | Louis |
+------------+------+-----------+
| 5 | C | Elizabeth |
+------------+------+-----------+
| 6 | B | Wayne |
+------------+------+-----------+
|
cs |
Finally, if we want to get the row_number group by column team
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SELECT @row_no := IF(@prev_val = t.team, @row_no + 1, 1) AS row_number
,@prev_val := t.team AS team
,t.person
FROM team_person t,
(SELECT @row_no := 0) x,
(SELECT @prev_val := '') y
ORDER BY t.team ASC,t.person DESC;
+============+======+===========+
| row_number | team | person |
+============+======+===========+
| 1 | A | Walter |
+------------+------+-----------+
| 2 | A | Louis |
+------------+------+-----------+
| 3 | A | John |
+------------+------+-----------+
| 1 | B | Wayne |
+------------+------+-----------+
| 2 | B | Smith |
+------------+------+-----------+
| 1 | C | Elizabeth |
+------------+------+-----------+
|
cs |
출처 : riptutorial.com/mysql/example/19572/row-number-and-group-by-using-variables-in-select-statement
'Database > MySQL' 카테고리의 다른 글
MySQL 특정날짜 사이의 월 목록 가져오기 (0) | 2020.11.04 |
---|---|
MySQL 가로데이터를 세로데이터로 전환하기 (0) | 2020.10.23 |
MySQL 특정날짜 사이의 날짜 목록 가져오기 (0) | 2020.10.20 |
계층구조 테이블에서 계층쿼리 구현 (0) | 2020.09.17 |