选择多个子查询

Question

我有子查询，需要返回不同的结果。每个子查询使用不同的聚合函数，如SUM()和COUNT(*)。我所做的就是用SELECT (SELECT subquery, SELECT subquery)封装它们，不确定这是否可能。

预期结果：

TwoYears 123 5

SELECT(
(SELECT
(SELECT SUM(AHT)
FROM
(
    SELECT 
        CASE 
            WHEN DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE()) >= 2 AND (DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE())) <= 2 OR (DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE())) <= 1
                THEN [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Value]
    END AS AHT
    FROM [AgentProfile_CRT].[dbo].[uvw_AHTMaster] WHERE [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Month] = 'January'  AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Year] = 2018 AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[AccountID] = 8 AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[LOBID] = 23
) f
WHERE AHT = AHT ) AS TwoYears),

(SELECT
(SELECT COUNT(*) AS TwoYears
FROM
(
    SELECT 
        CASE 
            WHEN DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE()) >= 2 AND (DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE())) <= 2 OR (DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE())) <= 1
                THEN 'Good'
    END AS Result
    FROM [AgentProfile_CRT].[dbo].[uvw_AHTMaster] WHERE [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Month] = 'January'  AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Year] = 2018 AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[AccountID] = 8 AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[LOBID] = 23
) f
WHERE Result = 'Good') AS TwoYears)
) a 

Answer 1

您需要使所有的联合，并删除您的一些子选择。

如下所示：

SELECT SUM(AHT) as TwoYears
                    FROM (
                        SELECT CASE 
                                WHEN DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE()) >= 2
                                    AND (DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE())) <= 2
                                    OR (DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE())) <= 1
                                    THEN [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Value]
                                END AS AHT
                        FROM [AgentProfile_CRT].[dbo].[uvw_AHTMaster]
                        WHERE [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Month] = 'January'
                            AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Year] = 2018
                            AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[AccountID] = 8
                            AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[LOBID] = 23
                        ) f
                    WHERE AHT = AHT

                    union all


                    SELECT COUNT(*) AS TwoYears
                    FROM (
                        SELECT CASE 
                                WHEN DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE()) >= 2
                                    AND (DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE())) <= 2
                                    OR (DATEDIFF(year, [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[HireDate], GETDATE())) <= 1
                                    THEN 'Good'
                                END AS Result
                        FROM [AgentProfile_CRT].[dbo].[uvw_AHTMaster]
                        WHERE [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Month] = 'January'
                            AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[Year] = 2018
                            AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[AccountID] = 8
                            AND [AgentProfile_CRT].[dbo].[uvw_AHTMaster].[LOBID] = 23
                        ) f
                    WHERE Result = 'Good'