SQL子查询中按Operator统计TranTypeId=7记录数的问题

原查询及结果 #

以下SQL用于按Operator分组统计TranTypeId=6的销售相关数据（Sales、Commission、Amount、Prem）：

SELECT Operator,
       COUNT(*) AS 'Sales',
       SUM(Commission) As 'Commission', 
       SUM(Fee + (2 * Commission)) As 'Amount',
       SUM(Prem) As 'Prem'
FROM (
    SELECT F.FormId,
           F.Prem,
           F.Agent AS Operator, -- 补充别名，否则外层无法引用Operator
           ((F.Prem/(1 + (112/100.00))) * ((PLS.Commission/100.00) * 50/100.00)) As 'Commission',
           CASE WHEN F.Prem!= PLPT.Charged THEN  ((F.Prem - PLPT.Charged) * (13.11/100.00)) 
                ELSE 0 
                END As 'Fee'
    FROM FORM F
    INNER JOIN PaymentTransaction PLPT ON F.FormId = PLPT.FormId
    INNER JOIN PublicScheme_backup PLS ON PLS.SchemeId = PLPT.SchemeId
    WHERE Agent IS NOT NULL 
    AND F.TranTypeId= 6
    AND Convert(date, F.Timestamp) BETWEEN '01 Jun 2024' AND '04 Jul 2024'
    AND F.FormId > 1950000 
) As X
GROUP BY Operator

查询结果：

Operator    Sales   Commission  Amount      Prem
--------------------------------------------------
8E          3       33.547300   94.640021   319.77

需求 #

新增Quotes字段，统计相同日期范围内每个Operator对应的TranTypeId=7的记录数，期望结果：

Operator    Sales   Commission  Amount      Prem    Quotes
------------------------------------------------------------------
8E          3       33.547300   94.640021   319.77  预期数值

问题重现 #

尝试以下SQL后，所有Operator的Quotes数值相同，未得到预期结果：

SELECT Operator,
        Count(*) AS 'Sales',
        SUM(Commission) AS 'Commission', 
        SUM(Fee + (2 * Commission)) AS 'Amount',
        SUM(Prem) AS 'Prem',
        (SELECT Count(*) 
         FROM Form 
         WHERE TranTypeId = 7 
              AND Convert(date,Datetimestamp)  BETWEEN '01 Jun 2024' AND '04 Jul 2024' 
              AND Operator = Operator
        ) AS 'Quotes'
FROM (
    SELECT F.FormId, 
           F.Prem,
           F.Agent AS Operator,
           ((F.Prem/(1 + (112/100.00))) * ((PLS.Commission/100.00) * 50/100.00)) AS 'Commission',
           CASE WHEN F.Prem!= PLPT.Charged THEN  ((F.Prem - PLPT.Charged) * (13.11/100.00)) 
                ELSE 0 
                END AS 'Fee'
    FROM FORM F
    INNER JOIN PaymentTransaction PLPT ON F.FormId = PLPT.FormId
    INNER JOIN PublicScheme_backup PLS ON PLS.SchemeId = PLPT.SchemeId
    WHERE Agent IS NOT NULL 
          AND F.TranTypeId= 6
          AND Convert(date,F.Timestamp) BETWEEN '01 Jun 2024' AND '04 Jul 2024'
          AND F.FormId > 1950000 
) AS X
GROUP BY Operator

错误原因 #

子查询中的Operator = Operator是自比较，数据库会判定为永真条件，导致子查询返回所有TranTypeId=7且符合日期条件的总记录数，而非按当前Operator过滤的结果。此外，子查询中日期字段写成Datetimestamp，与原查询的Timestamp不一致，需统一。

解决方案 #

方法一：修正关联子查询的字段引用 #

修改子查询的WHERE条件，使用外层表别名X区分Operator，并统一日期字段：

SELECT X.Operator,
       COUNT(*) AS 'Sales',
       SUM(X.Commission) AS 'Commission', 
       SUM(X.Fee + (2 * X.Commission)) AS 'Amount',
       SUM(X.Prem) AS 'Prem',
       (SELECT COUNT(*) 
        FROM Form F_quotes
        WHERE F_quotes.TranTypeId = 7 
              AND CONVERT(date, F_quotes.Timestamp) BETWEEN '01 Jun 2024' AND '04 Jul 2024' 
              AND F_quotes.Agent = X.Operator -- 关联外层的Operator
       ) AS 'Quotes'
FROM (
    SELECT F.FormId,
           F.Prem,
           F.Agent AS Operator,
           ((F.Prem/(1 + (112/100.00))) * ((PLS.Commission/100.00) * 50/100.00)) AS 'Commission',
           CASE WHEN F.Prem!= PLPT.Charged THEN  ((F.Prem - PLPT.Charged) * (13.11/100.00)) 
                ELSE 0 
                END AS 'Fee'
    FROM FORM F
    INNER JOIN PaymentTransaction PLPT ON F.FormId = PLPT.FormId
    INNER JOIN PublicScheme_backup PLS ON PLS.SchemeId = PLPT.SchemeId
    WHERE F.Agent IS NOT NULL 
          AND F.TranTypeId= 6
          AND CONVERT(date, F.Timestamp) BETWEEN '01 Jun 2024' AND '04 Jul 2024'
          AND F.FormId > 1950000 
) AS X
GROUP BY X.Operator

方法二：使用LEFT JOIN预统计Quotes（更高效） #

先预统计每个Operator的TranTypeId=7记录数，再与原查询结果关联，避免嵌套子查询的重复计算：

WITH QuotesStats AS (
    SELECT 
        Agent AS Operator,
        COUNT(*) AS Quotes
    FROM Form
    WHERE TranTypeId = 7
          AND CONVERT(date, Timestamp) BETWEEN '01 Jun 2024' AND '04 Jul 2024'
    GROUP BY Agent
)
SELECT 
    X.Operator,
    COUNT(*) AS 'Sales',
    SUM(X.Commission) AS 'Commission', 
    SUM(X.Fee + (2 * X.Commission)) AS 'Amount',
    SUM(X.Prem) AS 'Prem',
    COALESCE(QS.Quotes, 0) AS 'Quotes' -- 无对应数据时显示0
FROM (
    SELECT F.FormId,
           F.Prem,
           F.Agent AS Operator,
           ((F.Prem/(1 + (112/100.00))) * ((PLS.Commission/100.00) * 50/100.00)) AS 'Commission',
           CASE WHEN F.Prem!= PLPT.Charged THEN  ((F.Prem - PLPT.Charged) * (13.11/100.00)) 
                ELSE 0 
                END AS 'Fee'
    FROM FORM F
    INNER JOIN PaymentTransaction PLPT ON F.FormId = PLPT.FormId
    INNER JOIN PublicScheme_backup PLS ON PLS.SchemeId = PLPT.SchemeId
    WHERE F.Agent IS NOT NULL 
          AND F.TranTypeId= 6
          AND CONVERT(date, F.Timestamp) BETWEEN '01 Jun 2024' AND '04 Jul 2024'
          AND F.FormId > 1950000 
) AS X
LEFT JOIN QuotesStats QS ON X.Operator = QS.Operator
GROUP BY X.Operator, QS.Quotes

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