mysql - 一个非常高级的sql查询的问题

Question

我需要在 SQL 中进行高级选择，但我卡住了。

我有下表:

id | user_id | position | value
1  |   1     |    1     |   1
1  |   1     |    2     |   1
1  |   1     |    3     |   3
1  |   2     |    1     |   2
1  |   2     |    2     |   2
1  |   2     |    3     |   2
1  |   3     |    1     |   3
1  |   3     |    2     |   2
1  |   3     |    3     |   1

我需要一个查询，它给我一个这样排序的结果集:

1. 每个用户的总和(用户1:5，用户2:6，用户3:6)
2. 每个用户的位置 3 的值(用户 1:3，用户 2:2，用户 3:1)
3. 每个用户的位置 3 的值 + 位置 2 的值(用户 1:4，用户 2:4，用户 3:4)
4. 每个用户的位置 3 的值 + 位置 2 的值 + 位置 1 的值(用户 1:5，用户 2:6，用户 3:6)

这只是一个示例，该表实际上可以包含更多位置，所以我需要一个不在三个位置上硬编码的查询。

注意:每个 user_id 的位置数始终相同。在这个例子中它是三个，但我也可以截断表格并使用五个位置为每个用户添加数据。

一个丑陋的解决方案是假设永远不会超过十个位置，创建 pos1、pos2 等作为列，然后将它们相应地添加到查询中。如果您只使用三个位置，您会得到很多 NULL 值，并且最多只能使用十个位置。

我考虑过使用临时表，但也没有找到突破口。

你会怎么做？

Answer 1

I need a query that is not hard coded on three positions.

那你就不能输出列的小计了。 SQL 要求列在您准备查询时是固定的；您不能编写动态追加更多列的查询，因为它会发现数据中有多少不同的值。

但是，您可以输出动态数量的行。

SELECT t1.user_id, CONCAT(t1.position, '-', MAX(t2.position)) AS position_range, 
  SUM(t2.value) AS subtotal
FROM MyTable t1
INNER JOIN MyTable t2
  ON t1.user_id = t2.user_id AND t1.position <= t2.position
GROUP BY t1.user_id, t1.position;

输出是:

+---------+----------------+----------+
| user_id | position_range | subtotal |
+---------+----------------+----------+
|       1 | 1-3            |        5 |
|       1 | 2-3            |        4 |
|       1 | 3-3            |        3 |
|       2 | 1-3            |        6 |
|       2 | 2-3            |        4 |
|       2 | 3-3            |        2 |
|       3 | 1-3            |        6 |
|       3 | 2-3            |        3 |
|       3 | 3-3            |        1 |
+---------+----------------+----------+

在获取整个结果集后，您必须编写应用程序代码将其转换为列。

抱歉，无法在任何品牌的 RDBMS 中编写完全动态的数据透视查询。您有两个选择:

1. 编写代码根据数据生成 SQL，如@TimLehner 更新后的答案所示

2. 编写代码对通用查询进行后处理，就像我在上面显示的那样。