(如何)我可以在单个查询中按行/结果顺序对查询结果组进行编号? [英] (How) can I number query result groups by row/result order in a single query?

问题描述

我有一个查询，该查询当前返回具有以下属性的数据:

I have a query that currently returns data with the following attributes:

• 一个编号 A ，该编号保证在结果中唯一(不在源表中)；结果按A排序，但结果中的A值不一定是连续的.
• 键 B ，该键会重复显示多行，并将其标记为同一组的一部分.它与A来自同一张表.

• A number A which is guaranteed to be unique in the result (not in the source table); the result is ordered by A, but the values of A in the result are not necessarily continuous.
• A key B which is repeated for multiple rows, tagging them as part of the same group. It comes from the same table as A.

示例:

+--+-+-+
|id|A|B|
+--+-+-+
| 5|1|2|
|15|3|2|
|12|4|5|
|66|6|5|
| 2|7|2|
+--+-+-+

我在这里看到了答案，这些答案解释了如何在结果中返回行号.但是，我要做的是获得一个(最好是基于1的)订单号，同时为每个 B 保留不同的计数.在下表中， C 是期望的结果:

I've seen answers here which explain how to return the row number in the result. What I do need, however, is to obtain a (preferrably 1-based) order number while keeping a distinct count for each B. In the following table, C is the desired result:

+--+-+-+-+
|id|A|B|C|
+--+-+-+-+
| 5|1|2|1|
|15|3|2|2|
|12|4|5|1|
|66|6|5|2|
| 2|7|2|3|
+--+-+-+-+

这超出了我目前的SQL技能，因此我将感谢任何指针.包括指向现有答案的指针！

This goes a little beyond my current SQL skill, so I'll be thankful for any pointers. Including pointers to existing answers!

编辑:下面的两个答案在结果方面都效果很好(使用虚拟包装查询进行排序).谢谢大家的帮助.哪个是最有效的查询?考虑一下在我的特定用例中，从原始查询返回的行数永远不会非常大(比如说最多50行，甚至只是想象中的一部分).同样，原始查询具有用于从其他关系中获取数据的联接，尽管它们与排序或过滤无关.最后，所有结果都可能具有相同的 B ，或者每个结果都具有不同的 B -可以在任何方向或介于两者之间的任何地方进行./p>

Both answers below work equally well in terms of results (with a dummy wrapping query used for sorting). Thank you all for the help. Which would be the most efficient query? Consider that in my specific use case, the amount of rows returned from the original query is never very large (let's say up to 50 rows, and even that is a stretch of the imagination). Also, the original query has joins used for fetching data from other relations, although they are not relevant for sorting or filtering. Finally, it is possible for all results to have the same B, or for every one of them to have a distinct B - it can go either way or anywhere inbetween.

推荐答案

您基本上想要的是

What you basically want is the RANK() function. However, since it's not available in MySQL, you can simulate it with:

SELECT *
FROM (
  SELECT a, b, (CASE b 
                WHEN @partition THEN @rank := @rank + 1 
                ELSE @rank := 1 AND @partition := b END) AS c
  FROM tbl, (SELECT @rank := 0, @partition := '') tmp
  ORDER BY b, a
) tmp
ORDER BY a

DEMO (SQL小提琴).

DEMO (SQL Fiddle).

这篇关于(如何)我可以在单个查询中按行/结果顺序对查询结果组进行编号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！