使用CTE将多个结果集合并为一个具有单独列的结果集

Question

我正在尝试使用CTE将多个结果集合并为一个具有6列的结果集。

这是我开始的查询，它返回3个结果集，每个2行:

DECLARE @User AS VARCHAR(50)
SET @User = 'testuser'
DECLARE @LastYearEndDate AS DATETIME
DECLARE @LastMonthEndDate AS DATETIME
DECLARE @LastWeekEndDate AS DATETIME
SET @LastYearEndDate = (SELECT DATEADD(dd,-1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))
SET @LastMonthEndDate = (SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))
SET @LastWeekEndDate = (SELECT DATEADD(day, -1 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 7, GETDATE()))
SELECT SUM(os.db_Exp) 
    AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastYearEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'

SELECT SUM(os.db_Exp) 
    AS MonthToDateRevenue, 
    COUNT(*) AS MonthToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastMonthEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'
SELECT SUM(os.db_Exp) 
    AS WeekToDateRevenue, 
    COUNT(*) AS WeekToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastWeekEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'

输出:

  Result set 1:
    YearToDateRevenue | YearToDateCount
            120               3
  Result set 2:
    MonthToDateRevenue | MonthToDateCount
            75                2
  Result set 3:
    WeekToDateRevenue | WeekToDateCount
            18                1

也许有一个更简单的方法来做到这一点，因为我每次只切换一个参数，但这是我的尝试:

/* Same declarations as above */
WITH cte AS (
SELECT
    os.db_Exp
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastYearEndDate
    AND os.db_Date39 > @LastMonthEndDate
    AND os.db_Date39 > @LastWeekEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%')
 SELECT
(SELECT SUM(os.db_Exp) AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM cte
WHERE os.db_Date39 > @LastYearEndDate)
(SELECT SUM(os.db_Exp) AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM cte
WHERE  os.db_Date39 > @LastMonthEndDate)
(SELECT SUM(os.db_Exp) AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM cte
WHERE os.db_Date39 > @LastWeekEndDate)

现在我得到错误:

Msg 4104, Level 16, State 1, Line 28
The multi-part identifier "os.db_Date39" could not be bound.
Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "os.db_Exp" could not be bound.
Msg 116, Level 16, State 1, Line 30
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

输出:

 YearToDateRevenue | YearToDateCount | MonthToDateRevenue | MonthToDateCount | WeekToDateRevenue | WeekToDateCount
       120                  3                  75                  2                  18                  1

我不确定在cte中合并参数并使其运行的正确方法。任何我没有看到的更简单的解决方案也会受到赞赏。我只是希望每个和都在不同的列中计数。对于每个只返回一个数字，因此这应该返回一行和6列。由于

Answer 1

您可以使用CASE来实现这一点:

SELECT SUM(CASE WHEN os.db_Date39 > @LastYearEndDate THEN os.db_Exp ELSE 0 END) AS YearToDateRevenue, 
    SUM(CASE WHEN os.db_Date39 > @LastYearEndDate THEN 1 ELSE 0 END) AS YearToDateCount,
    SUM(CASE WHEN os.db_Date39 > @LastMonthEndDate THEN os.db_Exp ELSE 0 END) AS MonthToDateRevenue, 
    SUM(CASE WHEN os.db_Date39 > @LastMonthEndDate THEN 1 ELSE 0 END) AS MonthToDateCount,
    SUM(CASE WHEN os.db_Date39 > @LastWeekEndDate THEN os.db_Exp ELSE 0 END) AS WeekToDateRevenue, 
    SUM(CASE WHEN os.db_Date39 > @LastWeekEndDate THEN 1 ELSE 0 END) AS WeekToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'

Answer 2

有几种不同的方法可以做到这一点，但我认为最快的方法是使用这样的东西…它是对第一个查询的修改，应该可以让您快速启动并运行。有更好的方法来做到这一点，但根据你的问题和提供的信息，这将使你开始运行。

DECLARE @User AS VARCHAR(50)
SET @User = 'testuser'
DECLARE @LastYearEndDate AS DATETIME
DECLARE @LastMonthEndDate AS DATETIME
DECLARE @LastWeekEndDate AS DATETIME
SET @LastYearEndDate = (SELECT DATEADD(dd,-1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))
SET @LastMonthEndDate = (SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))
SET @LastWeekEndDate = (SELECT DATEADD(day, -1 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 7, GETDATE()))
with YTD_total as (
SELECT 1 as joinpoint
    SUM(os.db_Exp) 
    AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastYearEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'
),
MTD_total as (
SELECT 1 as joinpoint
    SUM(os.db_Exp) 
    AS MonthToDateRevenue, 
    COUNT(*) AS MonthToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastMonthEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'
),
WTD_total as (
SELECT 1 as joinpoint
    SUM(os.db_Exp) 
    AS WeekToDateRevenue, 
    COUNT(*) AS WeekToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastWeekEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'
)
select YearToDateRevenue,
       YearToDateCount,
       MonthToDateRevenue,
       MonthToDateCount
       WeekToDateRevenue,
       WeekToDateCount
from ytd_totals ytd
     join mtd_total mtd
         on mtd.joinpoint = ytd.joinpoint
     join wtd_total wtd
         on wtd.joinpoint = mtd.joinpoint