mysql - 我的两个查询分别工作，但是当我将一个查询嵌套在另一个查询中时，它不再工作

Question

我的第一个查询是:

SELECT 
SUM(CASE WHEN (Transactions.RegFunction = '1' AND Transactions.RegYear = "2017") THEN RegAmt END) AS GroupCurrsumFee, 
SUM(CASE WHEN (Transactions.RegFunction = '1' AND Transactions.RegYear = "2017") THEN Transactions.LMSCAmt END) AS IndCurrsumFee
FROM AllTransactions

我的第二个查询是:

SELECT GroupAmt 
FROM GroupFees
WHERE
'2016-11-01' BETWEEN BeginDate AND EndDate
AND RegYear = "2016"
AND GROUPID = "14"
AND RegFunction = 1;

当我运行该查询时，它返回以下内容:

|  GroupAmt  |
|   5.00     |

当我将第二个查询嵌套在第一个查询中以便它可以在列别名中返回该数据时，它不会显示。我将这两个查询组合起来并编写如下:

SELECT
SUM(CASE WHEN (Transactions.RegFunction = '1' AND Transactions.RegYear = "2017") THEN RegAmt END) AS GroupCurrsumFee, 
    SUM(CASE WHEN (Transactions.RegFunction = '1' AND Transactions.RegYear = "2017") THEN Transactions.LMSCAmt END) AS IndCurrsumFee,
(SELECT GroupAmt FROM GroupFees
    WHERE
    '2016-11-01' BETWEEN BeginDate AND EndDate
    AND RegYear = "2016"
    AND GROUPID = "14"
    AND RegFunction = 1) AS GroupFee
FROM AllTransactions

Answer 1

改用联接。考虑到查询可能返回 NULL 的可能性，我建议使用始终为 true 的连接条件的左连接。 (是的，有点“hacky”。​​)注意，不能保证子查询只返回一行。如果确实如此，您的总体结果可能不是您所期望的。

SELECT SUM(CASE 
        WHEN (
                Transactions.RegFunction = '1'
                AND Transactions.RegYear = "2017"
                )
            THEN RegAmt
        END) AS GroupCurrsumFee
, SUM(CASE 
        WHEN (
                Transactions.RegFunction = '1'
                AND Transactions.RegYear = "2017"
                )
            THEN Transactions.LMSCAmt
        END) AS IndCurrsumFee
   , GroupFee.GroupAmt
FROM AllTransactions
LEFT JOIN (
    SELECT GroupAmt
    FROM GroupFees
    WHERE '2016-11-01' BETWEEN BeginDate
            AND EndDate
        AND RegYear = '2016'
        AND GROUPID = '14'
        AND RegFunction = 1
    ) AS GroupFee on 1=1