子组如何在 sql 查询中添加生成的增量列? [英] How might sub-groups have a generated increment column added in an sql query?

问题描述

首先，这类似于这里更简单的 stack-o 问题，使用 MySQL，如何在表中生成包含记录索引的列?，其中向查询添加了通用增量列.

First, this is similar to the simpler stack-o question here, With MySQL, how can I generate a column containing the record index in a table?, where a general incremented column was added to a query.

但是，在这种情况下，我试图增加单个子组.

But, in this case I'm attempting to increment over individual sub-groups.

例如，考虑下表:

name    sub-group   note
john    1           yes
doe     1           no
bill    1           maybe
greg    2           so
dan     2           blue
jim     3           white

哪个查询会输出一个表，其中每个子组增加一个额外的列，如下所示:

What query would output a table with an additional column incremented per sub-group, like the following:

name    sub-group   note    increment
john    1           yes     1
doe     1           no      2
bill    1           maybe   3
greg    2           so      1
dan     2           blue    2
jim     3           white   1

这可能吗?

推荐答案

我已经解决了，感谢这里最优秀的博客文章的帮助:http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

I have solved it, thanks to help from a most excellent blog post here: http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

该解决方案非常重要，需要变量和一些有关 mysql 如何对其查询操作进行排序的高级知识，但它似乎相当高效.关键之一是变量赋值可以隐藏在函数调用中！

The solution is non-trivial, requiring variables and some advanced knowledge of how mysql orders its query operations, but it appears to be fairly performant. One of the keys is that variable assignments can be hidden within function calls!

本质上，下面的查询解决了这个问题:

Essentially, the following query solves the problem:

SET @num := 0, @type := '';

SELECT name, subgroup, @num AS increment
FROM table_name
WHERE 0 <= GREATEST(
   @num := IF(@type = subgroup, @num + 1, 1),
   LEAST(0, LENGTH(@type := subgroup)))

函数GREATEST、LEAST 和LENGTH 只是作为变量赋值的容器.如您所见，这些函数基本上没有影响查询的输出.

The functions GREATEST, LEAST, and LENGTH are just there as containers for variable assignments. As you can see, those functions are essentially doing nothing to affect the output of the query.

但是，我也发现我的表中有不连续的子组"值.例如:

However, I also found that I had "subgroup" values in my table that were not consecutive. For example:

+------+----------+
| name | subgroup |
+------+----------+
| john | 1        |
| doe  | 1        |
| jim  | 1        |
| greg | 2        |
| boe  | 2        |
| amos | 3        |
| ben  | 1        |
| gary | 2        |
+------+----------+

产生如下输出表:

+------+----------+-----------+
| name | subgroup | increment |
+------+----------+-----------+
| john | 1        |         1 |
| doe  | 1        |         2 |
| jim  | 1        |         3 |
| greg | 2        |         1 |
| boe  | 2        |         2 |
| amos | 3        |         1 |
| ben  | 1        |         1 |
| gary | 2        |         1 |
+------+----------+-----------+

由于执行顺序，将 ORDER BY 子句添加到查询末尾不起作用，并且隐藏 ORDER BY 子句中的变量赋值更接近但有它自己的问题，所以这是我使用的最终查询:

Tacking an ORDER BY clause onto the end of the query did not work because of the execution order, and hiding the variable assignments in the ORDER BY clause came closer but had its own issues, so here is the final query that I used:

SET @num := 0, @type := '';

SELECT name, subgroup, @num AS increment
FROM (SELECT * FROM table_name ORDER BY subgroup) AS table_name2
WHERE 0 <= GREATEST(
   @num := IF(@type = subgroup, @num + 1, 1),
   LEAST(0, LENGTH(@type := subgroup)))

产生以下输出:

+------+----------+-----------+
| name | subgroup | increment |
+------+----------+-----------+
| john | 1        |         1 |
| doe  | 1        |         2 |
| jim  | 1        |         3 |
| ben  | 1        |         4 |
| greg | 2        |         1 |
| boe  | 2        |         2 |
| gary | 2        |         3 |
| amos | 3        |         1 |
+------+----------+-----------+

耶！

这篇关于子组如何在 sql 查询中添加生成的增量列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！