SQL:我可以在窗口函数中引用/访问当前行的数据吗?

问题描述

这是一个例子.假设我有下表:

Here is an example. Suppose I have the following table:

 id     | list
--------+----------
 10     |
        | 10,20
 20     | 10,20

对于每个 id 值，我想计算具有包含该 id 值的 list 值的行数.结果应该是这样的:

For each id value, I'd like to calculate the number of rows having a list value which contains that id value. The result would be look like that:

 id     | count of lists
--------+----------
 10     | 2
        | 0
 20     | 2

我建议应该使用窗口函数，但似乎我无法从这样的函数中访问 id 值.

I suggest a window function should be used, but it seems that I can't access the id value from within such a function.

我完全同意这是糟糕的设计.这个问题是关于可能性的.

I totally agree that it is BAD design. This question is about the possibility.

任何 MySQL/PostgreSQL 解决方案都可以.

Any MySQL/PostgreSQL solution is fine.

推荐答案

在我开始之前，如上所述，这是一个糟糕的设计.但是，这就是我查询它的方式:

Before I start, as mentioned above, this is a poor design. However, this is how I would query it:

CREATE TABLE #Lists (id int, list varchar(500));
INSERT INTO #Lists (id, list) VALUES
    (10, NULL), (NULL, '10,20'), (20, '10,20');

WITH cte AS (
    SELECT LEFT(list, INSTR(list, '%,%')-1) AS value, SUBSTRING(list, INSTR(list, '%,%') + 1, 500) AS list FROM #Lists
    UNION ALL
    SELECT CASE WHEN list LIKE ('%,%') THEN LEFT(list, INSTR(list, '%,%')-1) ELSE list END AS value, CASE WHEN list LIKE ('%,%') THEN SUBSTRING(list, INSTR(list, '%,%') + 1, 500) END AS list FROM cte 
    WHERE CHAR_LENGTH(list) > 0
)

SELECT value, COUNT(*) FROM cte GROUP BY value;

DROP TABLE #Lists;

此解决方案允许 list 字符串中包含任意数量的值(例如10,20,30").

This solution allows for any number of values in the list string (like '10,20,30').

理想情况下，列表值应存储在单独的表中，以便每条记录都有一个值，例如 CREATE TABLE BetterDesign (id int, value int) INSERT INTO BetterDesign (id, value) VALUES (10, NULL), (NULL, 10), (NULL, 20), (20, 10), (20, 20).连同其他一百万个原因，这更适合查询 SELECT value, COUNT(*) FROM BetterDesign GROUP BY value.话虽如此，我理解遗留系统的痛苦.

Ideally, the list values should be stored in a separate table so that each record has a single value, such as CREATE TABLE BetterDesign (id int, value int) INSERT INTO BetterDesign (id, value) VALUES (10, NULL), (NULL, 10), (NULL, 20), (20, 10), (20, 20). Along with a million other reasons, this is better for querying SELECT value, COUNT(*) FROM BetterDesign GROUP BY value. That being said, I understand the pains of legacy systems.

这篇关于SQL:我可以在窗口函数中引用/访问当前行的数据吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！