WITH ROLLUP 只产生 NULL

2021-09-10 00:00:00 tsql sql-server rollup

这是表格正常"的样子

WorkloadCategory | WorkloadCapacity| WorkloadTotalTime
-----------------|-----------------|------------------
DI               | 317632          | 239.92
DI               | 106706          | 32.45
DI               | 35840           | 27.77
DI               | 50000           | 48.07
DI               | 8000            | 9.18
DI               | 29120           | 15.71
DI               | 0               | 0

使用以下查询:

SELECT
    wlc.WorkloadCategory,
    wl.WorkloadCapacity,
    ROUND(wl.WorkloadMinutes * wl.WorkloadCapacity / 60 / assum.WorkYearHours, 2) AS WorkloadTotalTime
FROM
    swam.Assumptions assum
CROSS JOIN 
    swam.WorkloadCategories wlc
INNER JOIN 
    swam.Workloads wl ON wlc.WorkloadCategoryID = wl.WorkloadCategoryID
ORDER BY
    wlc.WorkloadCategory

我想要做的只是将 WorkloadCapacity 和 WorkloadTotalTime 两列相加，并在表格底部有一个新行，显示每列的总和.

What I want to do is simply sum the two columns WorkloadCapacity and WorkloadTotalTime and have a new row at the bottom of the table that displays those sum for each column.

我希望某些列为 NULL，因为这就是 ROLLUP 的工作方式，是的，我知道如果需要，我需要指定列名以读取总计"......但我不明白的是为什么ROLLUP 的总和根本没有出现.

I expected some column to be NULL because that's how ROLLUP works and yes, I understand I would need to specify the column name to read 'Total" if I wanted.... But what I don't understand is why the sums from ROLLUP are not even showing up at all.

我使用本网站另一篇文章中的 GROUP BY GROUPING SET(或类似方法)尝试了类似的方法，但没有解决我遇到的问题.

I tried something similar using GROUP BY GROUPING SET (or something like that) from another post on this site, but it did not solve the issue I'm having.

SELECT
    wlc.WorkloadCategory,
    ROUND(wl.WorkloadMinutes * wl.WorkloadCapacity / 60 / assum.WorkYearHours, 2) AS WorkloadTotalTime
FROM
    swam.Assumptions assum
CROSS JOIN 
    swam.WorkloadCategories  wlc
INNER JOIN 
    swam.Workloads wl ON wlc.WorkloadCategoryID = wl.WorkloadCategoryID
GROUP BY
    wlc.WorkloadCategory, wl.WorkloadCapacity, 
    assum.WorkYearHours, wl.WorkloadMinutes WITH ROLLUP

输出表中全是 NULL！

The output table is just FULL of NULLS!

WorkloadCategory | WorkloadCapacity | WorkloadTotalTime
DI               | 0                | 0
DI               | 0                | NULL
DI               | 0                | NULL
DI               | 8000             | 9.18
DI               | 8000             | NULL
DI               | 8000             | NULL
DI               | 29120            | 15.71
DI               | 29120            | NULL
DI               | 29120            | NULL
DI               | 35840            | 27.77
DI               | 35840            | NULL
DI               | 35840            | NULL
DI               | 50000            | 48.07
DI               | 50000            | NULL
DI               | 50000            | NULL
DI               | 106706           | 32.45
DI               | 106706           | NULL
DI               | 106706           | NULL
DI               | 317632           | 239.92
DI               | 317632           | NULL
DI               | 317632           | NULL
DI               | NULL             | NULL

预先感谢您提供的任何帮助.

Thanks in advance for any help you can provide.

推荐答案

您的 WITH ROLLUP 不起作用，因为您已将每个数字字段的查询告知 GROUP 作为类别，但您没有告诉它如何总和.此外， WITH ROLLUP 将在 4 个字段中的每一个中汇总，但这不是您想要的.您最终只需要一个总数(也可能是每个类别的总数)，因此通过 GROUPING SETS，您可以告诉它您想要什么.

Your WITH ROLLUP isn't working because you've told the query to GROUP on each of the number fields as categories but you've not told it how to SUM the total. Further, WITH ROLLUP will roll up in each of the 4 fields but that's not what you want. You are after only a total at the end (and maybe a total per category) so with GROUPING SETS you can tell it what you want.

像这样:

SELECT
    wlc.WorkloadCategory,
    SUM(wl.WorkloadCapacity) AS WorkloadCapacity,
    SUM(ROUND(wl.WorkloadMinutes * wl.WorkloadCapacity / 60 / assum.WorkYearHours, 2)) AS WorkloadTotalTime
FROM
    swam.Assumptions assum
CROSS JOIN 
    swam.WorkloadCategories  wlc
INNER JOIN 
    swam.Workloads wl ON wlc.WorkloadCategoryID = wl.WorkloadCategoryID
GROUP BY GROUPING SETS (
  (wlc.WorkloadCategory, wl.WorkloadCapacity, assum.WorkYearHours, wl.WorkloadMinutes),
  (wlc.WorkloadCategory),
  ()
)

相关文章