如何使用汇总消除sql摘要行中的空格

yzckvree 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(267)

我在sql server 2016中有以下sql。我试图在列中捕获站点的总数，然后在行中使用汇总求和。但是，我也得到空值。我在这里提供示例数据：

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)

如何消除空值？有没有办法得到这样的结果：

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

sql sql-server count rollup

来源：https://stackoverflow.com/questions/62139029/how-to-eliminate-blanks-from-sql-summary-row-with-totals-with-rollup

2条答案

按热度按时间

fykwrbwg1#

你可以过滤 GROUP BY 通过 HAVING 使用 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 BY` )还是那个 `Code` 因此 `DepSite` 一定是太多了（因为你正在做一个 `ROLLUP` 不是一个 `CUBE` 以及 `Code` 先于 `DepSite` )

赞(0）回复(0）举报 2021-08-09

q35jwt9p2#

可以使用以下查询获得结果：

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 (Code is not null and DepSite is not null)
  or (Code is null and DepSite is null)

赞(0）回复(0）举报 2021-08-09

我来回答

如何使用汇总消除sql摘要行中的空格

2条答案

相关问题

热门标签

最新问答