时间戳

首页 > 解决方案 > 如何使用 ROLLUP 从带有总计的 SQL 汇总行中消除空白

sql - 如何使用 ROLLUP 从带有总计的 SQL 汇总行中消除空白

问题描述

我在 Sql server 2016 中有以下 SQL。我正在尝试捕获列中的站点总计，然后使用 ROLLUP 捕获行中的总计。但是，我也得到了空值。我在这里提供示例数据：

            create table #tempSite
                (
                Report_Date smalldatetime null,
                Code varchar(10) null,
                DepSite varchar(10) null
                )

                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'UVXYZ', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-22', 'UVXYZ', 'NC')

                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'PQRST', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'PQRST', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'UVXYZ', 'CA')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'PQRST', 'CA')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-23', 'UVXYZ', 'MD')

                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'PQRST', 'FL')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'ABCDE', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'PQRST', 'SC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'UVXYZ', 'NC')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'UVXYZ', 'CA')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'PQRST', 'CA')
                INSERT INTO #tempSite (Report_Date, Code, DepSite) values ('2020-05-24', 'UVXYZ', 'MD')

                SELECT Report_Date, Code, DepSite, count(DepSite) TOT_SITE
                FROM #tempSite  
                WHERE Code ='ABCDE'in 
                and Report_Date in ('2020-05-22', '2020-05-24')
                group by ROLLUP(Report_Date, Code, DepSite)

如何消除 NULLS？有没有办法获得如下结果：

Report_Date         Code    DepSite TOT_SITE
2020-05-22 00:00:00 ABCDE   FL      2
2020-05-22 00:00:00 ABCDE   NC      3
2020-05-22 00:00:00 ABCDE   SC      3
2020-05-22 00:00:00 NULL    NULL    8
2020-05-24 00:00:00 ABCDE   FL      1
2020-05-24 00:00:00 ABCDE   NC      3
2020-05-24 00:00:00 ABCDE   SC      3
2020-05-24 00:00:00 NULL    NULL    7
NULL                NULL    NULL    15

标签： sqlsql-servercountrollup

解决方案

您可以使用以下函数过滤GROUP BYviaHAVING子句：GROUPING

SELECT Report_Date, Code, DepSite, count(DepSite) TOT_SITE
FROM #tempSite  
WHERE Code ='ABCDE'
and Report_Date in ('2020-05-22', '2020-05-24')
group by ROLLUP(Report_Date, Code, DepSite)
having grouping(DepSite) = 0
or grouping (Code) = 1

GROUPING当列在该行中作为ROLLUP. 因此，您在这里检查最后一列( )是否DepSite未ROLLUP由)GROUP BYCodeDepSiteROLLUPCUBECodeDepSite

推荐阅读