mysql - Correlated Subqueries - Counting records "less than"主查询记录

Question

我试图通过计算给定记录之前并具有某些匹配特征的记录数来确定任何给定记录是哪个“迭代”。本质上，我想知 Prop 有相同父记录和类别且 ID# 小于 [my record's ID#] 的记录数。我可以获得相关子查询来计算具有相同父级和类别的记录数，但是当我尝试添加 ID 过滤器时，我的子查询过滤器找不到列“T1.ID” - 来 self 的 ID# 字段主表。

我的 SQL 如下，星号是我尝试但未能根据“T1 的”记录 ID 限制结果的地方(我显然在我的实际 SQL 中排除了星号)

select T1.ID, T2.ITERATION
from TICKET as T1
inner join
(select COUNT(T3.ID) as ITERATION, T3.CATEGORY_ID, T3.PARENT_ID 
from TICKET as T3 
where T3.IS_PARENT = 0 **AND T3.ID < T1.ID**
group by T3.PARENT_ID, T3. CATEGORY_ID)
as T2 on (T1.PARENT_ID = T2.PARENT_ID AND
T1. CATEGORY_ID = T2.CATEGORY_ID)
where T1.IS_PARENT = 0

在其他程序中，我使用了构建子查询的函数来实现类似的结果，所以如果我应该朝那个方向看，我会很感激你的反馈。

我从同一张表中计算，所以示例数据:

ID   PARENT_ID    CATEGORY
10   1            A
11   2            A
12   1            B
13   3            A
14   2            A
15   1            A
16   3            B
17   1            A

和期望的输出:

ID   ITERATION    (explanation)
10   0            (No preceding ID with same parent & category)
11   0            (No preceding ID with same parent & category)
12   0            (No preceding ID with same parent & category)
13   0            (No preceding ID with same parent & category)
14   1            (ID 11 precedes 14 and shares parent & category)
15   1            (ID 10 precedes 15 and shares parent & category)
16   0            (No preceding ID with same parent & category)
17   2            (ID's 10, 15 precede 17 and share parent & category)

Answer 1

我想这就是你想要的:

SELECT 
    ID, 
    PARENT_ID, 
    CATEGORY, 
    (SELECT COUNT(*) FROM TICKET 
     WHERE ID < T.ID 
          AND CATEGORY = T.CATEGORY 
          AND PARENT_ID = T.PARENT_ID
     ) AS ITERATION
FROM TICKET T
ORDER BY ID

使用您的示例数据的结果:

ID  PARENT_ID   CATEGORY    ITERATION
10  1           A           0
11  2           A           0
12  1           B           0
13  3           A           0
14  2           A           1
15  1           A           1
16  3           B           0
17  1           A           2