sql - 汇总未按预期聚合

Question

情况：

我想Rollup在我的输出末尾使用，但它似乎多次添加相同的数字，因此夸大了总量。

我的查询如下所示：

; with cte as (
    SELECT
          cast(point.date as date) AS target_date
        , 'all' AS [site]
        , 'pc' AS device
        , point.id
        , point.category
        , point.command 
        , staff
        , point.point
    FROM table.banana  AS point
    LEFT JOIN table.orange AS staff ON point.id=staff.id
    WHERE
       CAST(point.date AS date) = '2019-05-26' 
     )


 -- base query 
     SELECT
          base.target_date
        , base.[site]
        , base.device
        , SUM(case when cte.category = 'Bonus' 
                and cte.type = 'free' 
                and cte.command = 'add' 
                and cte.staff is null then cte.point else 0 end) as total
    FROM ( -- this is just a mapping
        SELECT
              (SELECT CAST(key_value AS DATE) FROM baseDate WHERE [KEY_NAME] = 'target_date') AS target_date 
            , [target].[site]
            , [target].[device]
        FROM ( 
            SELECT 'net' AS [site], 'pc' AS device
            UNION ALL SELECT 'net','android_app'
            UNION ALL SELECT 'com','pc'
            UNION ALL SELECT 'com','android_app'
        ) AS [target]) AS base
    LEFT JOIN cte 
        ON base.target_date = cte.target_date 
            AND base.device = cte.device
    GROUP BY      
          base.target_date
        , base.[site]
        , base.device
    WITH ROLLUP 
    GO

我的输出是：

+-------------+------+-------------+----------------------------+
| target_date | site |   device    |           total             |
+-------------+------+-------------+----------------------------+
| 5/26/2019   | com  | android_app |                       -    |
| 5/26/2019   | com  | pc          |                200,000.00  |
| 5/26/2019   | com  | NULL        |                200,000.00  |
| 5/26/2019   | net  | android_app |                       -    |
| 5/26/2019   | net  | pc          |                200,000.00  |
| 5/26/2019   | net  | NULL        |                200,000.00  |
| 5/26/2019   | NULL | NULL        |                400,000.00  |
| NULL        | NULL | NULL        |                400,000.00  |
+-------------+------+-------------+----------------------------+

我的预期输出应该是： 请注意，结构看起来是这样的，因为我有 10 个其他视图必须加入到我的基本查询中。

+-------------+------+-------------+----------------------------+
| target_date | site |   device    |            total           |
+-------------+------+-------------+----------------------------+
| 5/26/2019   | com  | android_app |                       -    |
| 5/26/2019   | com  | pc          |                200,000.00  |
| 5/26/2019   | com  | NULL        |                200,000.00  |
| 5/26/2019   | net  | android_app |                       -    |
| 5/26/2019   | net  | pc          |                200,000.00  |
| 5/26/2019   | net  | NULL        |                200,000.00  |
| 5/26/2019   | NULL | NULL        |                200,000.00  |
| NULL        | NULL | NULL        |                200,000.00  |
+-------------+------+-------------+----------------------------+

标签： sqlsql-server

Answer 1

汇总工作正常。

您在连接中缺少连接条件site：

LEFT JOIN cte
ON base.target_date = cte.target_date 
            AND base.device = cte.device

这会导致重复值。