如何合并兩個選擇查詢，其列數基本相同，但一組回傳多行-有解無憂

我有兩個可以完美運行的查詢：

DECLARE @StartDate DATETIME = '2021-11-01 00:00:00';
DECLARE @EndDate DATETIME = '2022-03-16 23:59:59';
DECLARE @SalesEstimateTransactionTypeId INT = 16;
DECLARE @SalesOrderTransactionTypeId INT = 15;

SELECT 
    DATENAME(mm, GeneralJournal.[TransactionDate]) AS ReportingMonth, 
    DATEPART(mm, GeneralJournal.[TransactionDate]) AS MonthNumber, 
    DATEPART(yyyy, GeneralJournal.[TransactionDate]) AS ReportingYear,
    COUNT(TransactionId) AS TransactionCount
FROM 
    GeneralJournal 
WHERE 
    GeneralJournal.[TransactionDate] >= @StartDate 
    AND GeneralJournal.[TransactionDate] <= @EndDate 
    AND MasterRecord = 1 
    AND TransactionTypeId = @SalesEstimateTransactionTypeId
GROUP BY 
    DATEPART(yyyy, GeneralJournal.[TransactionDate]), 
    DATEPART(mm, GeneralJournal.[TransactionDate]),  
    DATENAME(mm,GeneralJournal.[TransactionDate]);

SELECT 
    DATENAME(mm, GeneralJournal.[TransactionDate]) AS ReportingMonth, 
    DATEPART(mm, GeneralJournal.[TransactionDate]) AS MonthNumber, 
    DATEPART(yyyy, GeneralJournal.[TransactionDate]) AS ReportingYear, 
    COUNT(DISTINCT TransactionId) AS ConversionCount
FROM 
    GeneralJournal 
WHERE 
    GeneralJournal.[TransactionDate] >= @StartDate 
    AND GeneralJournal.[TransactionDate] <= @EndDate 
    AND MasterRecord = 0 
    AND TransactionTypeId = @SalesOrderTransactionTypeId 
    AND SEReferenceId > 0
GROUP BY 
    DATEPART(yyyy, GeneralJournal.[TransactionDate]), 
    DATEPART(mm, GeneralJournal.[TransactionDate]),  
    DATENAME(mm,GeneralJournal.[TransactionDate]);

請注意，第二個查詢回傳 distinct，因為它可以回傳多個值，并且我們只想TransactionId在這種情況下對每個值計數一次。這些回傳以下結果：

報告月	月數	報告年	事務計數
十一月	11	2021	82
十二月	12	2021	49
一月	1	2022	64
二月	2	2022	67
行進	3	2022	49

報告月	月數	報告年	轉化次數
十一月	11	2021	42
十二月	12	2021	27
一月	1	2022	31
二月	2	2022	50
行進	3	2022	24

我實際上需要像這樣組合它們：

報告月	月數	報告年	事務計數	轉化次數
十一月	11	2021	82	42
十二月	12	2021	49	27
一月	1	2022	64	31
二月	2	2022	67	50
行進	3	2022	49	24

我已經嘗試了幾乎所有我能想到的東西——聯合、連接、子查詢——但到目前為止，沒有什么是完全正確的。這是我能得到的最接近的：

SELECT 
    DATENAME(mm, GeneralJournal.[TransactionDate]) AS ReportingMonth, 
    DATEPART(mm, GeneralJournal.[TransactionDate]) AS MonthNumber, 
    DATEPART(yyyy, GeneralJournal.[TransactionDate]) AS ReportingYear, 
    SUM(CASE 
            WHEN TransactionTypeId = @SalesEstimateTransactionTypeId 
                 AND MasterRecord = 1 
               THEN 1 ELSE 0 
        END) AS TransactionCount, 
    COUNT(CASE 
              WHEN TransactionTypeId = @SalesOrderTransactionTypeId  
                   AND SEReferenceId > 0 THEN 1 
          END) AS ConversionCount
FROM 
    GeneralJournal 
WHERE 
    GeneralJournal.[TransactionDate] >= @StartDate 
    AND GeneralJournal.[TransactionDate] <= @EndDate 
    AND TransactionTypeId IN (@SalesOrderTransactionTypeId, @SalesEstimateTransactionTypeId)
GROUP BY 
    DATEPART(yyyy, GeneralJournal.[TransactionDate]), 
    DATEPART(mm, GeneralJournal.[TransactionDate]),    
    DATENAME(mm,GeneralJournal.[TransactionDate]);

但是，我無法找到一種方法來獲取ConversionCount. 結果，它回傳了完整計數：

報告月	月數	報告年	事務計數	轉化次數
十一月	11	2021	82	152
十二月	12	2021	49	67
一月	1	2022	64	101
二月	2	2022	67	136
行進	3	2022	49	64

Can anyone guide me towards a way to combine the two query results whilst maintaining the Distinct on the conversion count? I must add that for it to work the answer must be compatible with both SQL Server and VistaDB the syntax of which is a subset of T-SQL because I am obliged to support both database engines with the same query.

EDIT - The Final Solution

Following on from Nick's excellent answer I was able embed the solution into my existing query code to ensure that there are results even for months with no records, shown here in case it helps anyone else:

DECLARE @StartDate DATETIME = '2021-11-01T00:00:00';
DECLARE @EndDate DATETIME = '2022-10-31T23:59:59';
DECLARE @SalesEstimateTransactionTypeId INT = 16;
DECLARE @SalesOrderTransactionTypeId INT = 15;

DECLARE @CurrentDate DATETIME;
DECLARE @Months TABLE(ReportingYear INT, MonthNumber INT, ReportingMonth VARCHAR (40));

-- Set the initial date
SET @CurrentDate = @StartDate
-- insert all dates into temp table
WHILE @CurrentDate <=  @EndDate
BEGIN
    INSERT INTO @Months VALUES(DATEPART(year, @CurrentDate), DATEPART(month, @CurrentDate), DATENAME(mm, @CurrentDate))
    SET @CurrentDate = dateadd(mm, 1, @CurrentDate)
END;

SELECT ReportingMonth, ReportingYear, Coalesce(TransactionCount, 0) AS TransactionCount, Coalesce(ConversionCount,0) AS ConversionCount
FROM
(
    SELECT months.[ReportingMonth], months.[ReportingYear], conversionData.[TransactionCount], conversionData.[ConversionCount]
    FROM @Months months
    LEFT JOIN
    (
        SELECT
        ReportingMonth      = DATENAME(mm, GeneralJournal.[TransactionDate]),
        MonthNumber         = DATEPART(mm, GeneralJournal.[TransactionDate]),
        ReportingYear       = DATEPART(yyyy, GeneralJournal.[TransactionDate]),
        TransactionCount    = SUM(CASE WHEN TransactionTypeId = @SalesEstimateTransactionTypeId AND GeneralJournal.[MasterRecord] = 1 THEN
                                        1
                                    ELSE
                                        0
                                END
                            ),
        ConversionCount     = COUNT(DISTINCT CASE WHEN GeneralJournal.[TransactionTypeId] = @SalesOrderTransactionTypeId
                                        AND GeneralJournal.[SEReferenceId] > 0
                                        AND GeneralJournal.[MasterRecord] = 0 THEN
                                        GeneralJournal.[TransactionID]
                                END
                            )
        FROM GeneralJournal
        WHERE GeneralJournal.[TransactionDate] >= @StartDate
            AND GeneralJournal.[TransactionDate] <= @EndDate
            AND GeneralJournal.[TransactionTypeId] IN ( @SalesOrderTransactionTypeId, @SalesEstimateTransactionTypeId)
        GROUP BY
            DATEPART(yyyy, GeneralJournal.[TransactionDate]),
            DATEPART(mm, GeneralJournal.[TransactionDate]),
            DATENAME(mm, GeneralJournal.[TransactionDate])
    ) as conversionData
    ON months.[ReportingYear] = conversionData.[ReportingYear] AND months.[MonthNumber] = conversionData.[MonthNumber]
) AS data;

uj5u.com熱心網友回復：

您可以將兩列放在同一個查詢中。WHERE由于條款略有不同，這使情況變得更加復雜。所以你需要分組，然后再次分組，并使用條件聚合來計算每列的正確行數。

請注意以下事項：

理論上你可以這樣做，COUNT(DISTINCT CASE但是通常會更慢，因為編譯器不會識別CASE它在做什么，而是做一個完整的排序。
按單個EOMONTH計算分組到按整月分組更快。您可以在SELECT.
COUNT(TransactionId)將回傳非空TransactionId值的數量。如果TransactionId不能為空，那么COUNT(*)是同一件事。
如果TransactionDate有時間分量，那么你應該使用半開區間>= AND <
在表上使用別名，它使您的查詢更具可讀性。
使用 whitepsace，它是免費的。

DECLARE @StartDate DATETIME = '2021-11-01T00:00:00';
DECLARE @EndDate DATETIME = '2022-03-17T00:00:00';
DECLARE @SalesEstimateTransactionTypeId INT = 16;
DECLARE @SalesOrderTransactionTypeId INT = 15;

SELECT
  DATENAME(month, gj.mth) AS ReportingMonth,
  DATEPART(month, gj.mth) AS MonthNumber,
  DATEPART(year , gj.mth) AS ReportingYear,
  SUM(TransactionCount) AS TransactionCount,
  COUNT(CASE WHEN ConversionCount > 0 THEN 1 END) AS ConversionCount
FROM (
    SELECT
      EOMONTH(gj.TransactionDate) AS mth,
      gj.TransactionId,
      COUNT(CASE WHEN gj.MasterRecord = 1 AND gj.TransactionTypeId = @SalesEstimateTransactionTypeId THEN 1 END) AS TransactionCount,
      COUNT(CASE WHEN gj.MasterRecord = 0 AND gj.TransactionTypeId = @SalesOrderTransactionTypeId AND gj.SEReferenceId > 0 THEN 1 END) AS ConversionCount
    FROM GeneralJournal gj
    WHERE gj.TransactionDate >= @StartDate
      AND gj.TransactionDate <  @EndDate
      AND gj.TransactionTypeId IN (@SalesOrderTransactionTypeId, @SalesEstimateTransactionTypeId)
    GROUP BY
      EOMONTH(gj.TransactionDate),
      TransactionId
) g
GROUP BY
  mth;

uj5u.com熱心網友回復：

您的第二個查詢很接近，我認為只有幾個小遺漏。

您忘記MasterRecord = 0了 ConversionCountCASE陳述句。
而不是從您的 ConversionCount 回傳 1 或 0，您CASE應該回傳 TransactionID 或 NULL，以便您仍然可以計算不同的值。
DISTINCT您在 ConversionCount中丟失了COUNT。
您將需要處理 ConversionCount 中的 NULL 值COUNT。我假設你總是有一個或多個NULLs，所以我只是從中減去 1COUNT(DISTINCT ...)來補償。

（如果沒有一些示例詳細資料可以使用，我不能 100% 了解這里的語法。）

代碼

SELECT
    ReportingMonth      = DATENAME(mm, GeneralJournal.TransactionDate),
    MonthNumber         = DATEPART(mm, GeneralJournal.TransactionDate),
    ReportingYear       = DATEPART(yyyy, GeneralJournal.TransactionDate),
    TransactionCount    = SUM(CASE
                                WHEN TransactionTypeId = @SalesEstimateTransactionTypeId
                                    AND MasterRecord = 1 THEN
                                    1
                                ELSE
                                    0
                            END
                        ),
    ConversionCount     = COUNT(DISTINCT CASE
                                WHEN TransactionTypeId = @SalesOrderTransactionTypeId
                                    AND SEReferenceId > 0
                                    AND MasterRecord = 0 THEN
                                    TransactionID
                                ELSE
                                    NULL
                            END
                        ) - 1 /* Subtract 1 for the NULL */
FROM    GeneralJournal
WHERE
    GeneralJournal.TransactionDate >= @StartDate
    AND GeneralJournal.TransactionDate <= @EndDate
    AND TransactionTypeId IN (
            @SalesOrderTransactionTypeId,
            @SalesEstimateTransactionTypeId
        )
GROUP BY
    DATEPART(yyyy, GeneralJournal.TransactionDate),
    DATEPART(mm, GeneralJournal.TransactionDate),
    DATENAME(mm, GeneralJournal.TransactionDate);

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/446843.html

標籤：sql-server tsql vistadb

上一篇：SQLServer：如何合并或分組行？

下一篇：以非美國格式格式化電話號碼