SQL查询求助：如何正确统计TOOL_ID总数并返回指定字段？

原查询的核心问题是：你将ITEM_SPEC_ID和TOOL_ID共同作为分组条件，导致COUNT(b.TOOL_ID)统计的是每个(ITEM_SPEC_ID, TOOL_ID)组合的出现次数。但由于Table2的主键是这两个字段，每个组合只会存在一次，所以结果里的num_tool_id始终为1，无法实现统计每个ITEM_SPEC_ID下TOOL_ID总数量的需求。

以下是两种可行的修正方案：

方法一：使用窗口函数（推荐） #

窗口函数可以在保留原有行结构的前提下，直接计算每个ITEM_SPEC_ID对应的TOOL_ID总数：

SELECT
    a.ITEM_SPEC_ID,
    b.TOOL_ID,
    COUNT(b.TOOL_ID) OVER (PARTITION BY a.ITEM_SPEC_ID) AS num_tool_id
FROM
    Table1 a
JOIN
    Table2 b ON a.ITEM_SPEC_ID = b.ITEM_SPEC_ID

其中PARTITION BY a.ITEM_SPEC_ID会将数据按ITEM_SPEC_ID分组，COUNT()会计算每个分组内的TOOL_ID总数，并将该总数附加到分组内的每一行上。

方法二：先统计再关联 #

先单独统计每个ITEM_SPEC_ID的TOOL_ID数量，再与Table2关联获取对应TOOL_ID：

SELECT
    b.ITEM_SPEC_ID,
    b.TOOL_ID,
    t.num_tool_id
FROM
    Table2 b
JOIN (
    SELECT
        ITEM_SPEC_ID,
        COUNT(TOOL_ID) AS num_tool_id
    FROM
        Table2
    GROUP BY
        ITEM_SPEC_ID
) t ON b.ITEM_SPEC_ID = t.ITEM_SPEC_ID
JOIN
    Table1 a ON b.ITEM_SPEC_ID = a.ITEM_SPEC_ID

子查询t先计算每个ITEM_SPEC_ID的TOOL_ID总数，再通过ITEM_SPEC_ID关联到Table2的每一行；若仅需ITEM_SPEC_ID和TOOL_ID字段，甚至可以去掉与Table1的关联（Table2已包含ITEM_SPEC_ID）。

这两种方法都能满足你的需求：当某个ITEM_SPEC_ID对应1个TOOL_ID时，该行的num_tool_id为1；对应2个TOOL_ID时，两行的num_tool_id均为2。

内容的提问来源于stack exchange，提问作者D T