MySQL 그룹별 시퀀스 번호 지정하기
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
MySQL - Row Number and Group By using variables in Select Statement | mysql Tutorial
mysql documentation: Row Number and Group By using variables in Select Statement
riptutorial.com
[MySQL] ROWNUM을 사용하여 번호매기기
MySQL에서 Oracle처럼 ROWNUM 사용법 SET구문을 사용하여 ROWNUM 값을 초기화 후 조회 SET @rownum:=0; SELECT @rownum:=@rownum+1, b.* FROM buyingboard b WHERE절에서 초기화 SELECT @rownum:=@rownum+1, b.*..
developer-jjun.tistory.com