MySQL mysql中每组的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17939198/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Row number per group in mysql
提问by user1852837
I want to produce query result base on this scenario that can create row number according to crew_id and type.
我想根据这个场景生成查询结果，可以根据crew_id和type创建行号。
id crew_id amount type
1 4 1000 AUB
2 4 1500 AUB
3 5 8000 CA
4 4 1000 CA
5 5 1000 AUB
6 6 3000 AUB
7 4 2000 CA
8 6 3500 AUB
9 4 5000 AUB
10 5 9000 CA
11 5 1000 CA
OUTPUT must be the ff:
OUTPUT 必须是 ff：
id crew_id amount type row_number
1 4 1000 AUB 1
2 4 1500 AUB 2
9 4 5000 AUB 3
4 4 1000 CA 1
7 4 2000 CA 2
5 5 1000 AUB 1
3 5 8000 CA 1
10 5 9000 CA 2
11 5 1000 CA 3
6 6 3000 AUB 1
6 6 3000 AUB 2
I want a single select statement only in this output
我只想要这个输出中的单个 select 语句
采纳答案by Rustam Guliev
The question is quite old. But I would like to post it in case someone will have a same problem.
这个问题很老了。但我想发布它，以防有人遇到同样的问题。
First of all, described answers do not work correct. For example, for
首先，描述的答案不正确。例如，对于
id crew_id amount type
1 4 1000 AUB
2 4 1500 AUB
5 5 1000 AUB
6 6 3000 AUB
8 6 3500 AUB
9 4 5000 AUB
(I just removed rows with type 'CA') the result table will be
（我刚刚删除了类型为“CA”的行）结果表将是
id crew_id amount rank type
1 4 1000 1 AUB
2 4 1500 2 AUB
9 4 5000 3 AUB
5 5 1000 4 AUB
6 6 3000 5 AUB
8 6 3500 6 AUB
So in fact it doesn't use both crew_id and type, it just uses type.
所以实际上它并没有同时使用crew_id 和type，它只使用type。
Here is how I solved this problem (probably there is a more elegant way to do it than use two nested 'CASE's, but you get the idea):
这是我解决这个问题的方法（可能有一种比使用两个嵌套的“CASE”更优雅的方法，但你明白了）：
SELECT id,
amount,
CASE crew_id
WHEN @curCrewId THEN
CASE type
WHEN @curType THEN @curRow := @curRow + 1
ELSE @curRow := 1
END
ELSE @curRow :=1
END AS rank,
@curCrewId := crew_id AS crew_id,
@curType := type AS type
FROM Table1 p
JOIN (SELECT @curRow := 0, @curCrewId := 0, @curType := '') r
ORDER BY crew_id, type
The main idea remain. I just added a variable @curCrewId. If someone need to use 3 variables for grouping, so just use 3 variables and 3 nested 'CASE's. :)
主要思想仍然存在。我刚刚添加了一个变量@curCrewId。如果有人需要使用 3 个变量进行分组，那么只需使用 3 个变量和 3 个嵌套的“CASE”。:)
回答by Janty
Please go through my fiddle
请通过我的小提琴
SELECT id,
crew_id,
amount,
type,
(
CASE type
WHEN @curType
THEN @curRow := @curRow + 1
ELSE @curRow := 1 AND @curType := type END
) + 1 AS rank
FROM Table1 p,
(SELECT @curRow := 0, @curType := '') r
ORDER BY crew_id,type asc;
回答by SystemParadox
@Janty's answer does not work when the type starts with a number (the rank starts at 2 instead of 1).
当类型以数字开头（排名从 2 而不是 1 开始）时，@Janty 的答案不起作用。
Use the following instead:
请改用以下内容：
SELECT id,
crew_id,
amount,
CASE type
WHEN @curType THEN @curRow := @curRow + 1
ELSE @curRow := 1
END AS rank,
@curType := type AS type
FROM Table1 p
JOIN (SELECT @curRow := 0, @curType := '') r
ORDER BY crew_id, type
回答by phil
In addition to the answer of @Janty here is a solution if you want to UDATE your table with the rownumber:
除了@Janty 的答案之外，如果您想使用行号对表进行 UDATE，这里还有一个解决方案：
UPDATE myTable mt,(SELECT @curRow := 0, @curType := '') r SET type=
(
CASE type
WHEN @curType
THEN @curRow := @curRow + 1
ELSE @curRow := 1 AND @curType := type END
)
;
As janty too crewId is not within. Use a second "case" for that as mentioned in the other answers.
由于 janty 太crewId 不在其中。如其他答案中所述，使用第二个“案例”。
回答by Abbas Hosseini
Here is my answer using only one casethat creates row number according to both columns:
这是我仅使用一种根据两列创建行号的情况的答案：
SELECT id, crew_id, amount, type,
(CASE CONCAT(crew_id, type)
WHEN @cur_crew_type
THEN @curRow := @curRow + 1
ELSE @curRow := 0 END) + 1 AS cnt,
@cur_crew_type := CONCAT(crew_id, type) AS cur_crew_type
FROM TABLE t,
(SELECT @curRow := 0, @cur_crew_type := '') counter
ORDER BY crew_id, type;
回答by user1852837
SELECT id, crew_id, amount, type,
(
CASE type
WHEN @curType
THEN @curRow := @curRow + 1
ELSE @curRow := 1 AND @curType := type END
) + 1 AS rank
FROM Table1 p,
(SELECT @curRow := 0, @curType := '') r
ORDER BY crew_id, type asc;
