用于统计的 SQL 独立数据集 - 数据表的集合
本文关键字:数据表 数据集 集合 独立 统计 SQL 用于 | 更新日期: 2023-09-27 17:56:52
在查询(MainQuery,对于样本来说并不复杂)中,我需要不同字段上的计数器,有没有办法让这些计数器出现在单独的数据集中而不是在所有行中?感谢您的帮助。
WITH
MainQuery AS(
SELECT ROW_NUMBER() OVER (ORDER BY tb17.[NO]) AS OrderId,
* FROM [OFFRES] tb17),
Counters as (SELECT *,
(select MAX(OrderId) FROM MainQuery ) AS 'TotalRecs',
sum(case when [TypeLoc]=215 then 1 else 0 end) over() Loc215,
sum(case when [TypeLoc]=220 then 1 else 0 end) over() Loc220,
sum(case when [TypeLoc]=526 then 1 else 0 end) over() Loc526,
sum(case when [TypeLoc]=209 then 1 else 0 end) over() Loc209,
sum(case when [TypeLoc]=223 then 1 else 0 end) over() Loc223
/*...*/
from MainQuery
)
SELECT * FROM Counters WHERE OrderId BETWEEN 1 AND 20000
一个数据集中的图输出:
OrderId No TypeLoc TotalRecs Loc215 Loc220 Loc526 Loc209 Loc223
1 399 218 28097 1299 1349 1324 1346 1329
2 403 223 28097 1299 1349 1324 1346 1329
3 404 223 28097 1299 1349 1324 1346 1329
4 405 223 28097 1299 1349 1324 1346 1329
5 406 223 28097 1299 1349 1324 1346 1329
我想要的输出:
首先 - 所有结果行:
OrderId No TypeLoc
1 399 218
2 403 223
3 404 223
4 405 223
5 406 223
第二 - 连续所有统计数据:
TotalRecs Loc215 Loc220 Loc526 Loc209 Loc223
28097 1299 1349 1324 1346 1329
在不知道您的表结构的情况下,我会说在两个单独的 select 语句中执行此操作并仅选择适当的列
WITH
MainQuery AS(
SELECT ROW_NUMBER() OVER (ORDER BY tb17.[NO]) AS OrderId,
* FROM [OFFRES] tb17)
SELECT OrderId, No, TypeLoc
FROM MainQuery
WHERE OrderId BETWEEN 1 AND 20000
和
WITH
MainQuery AS(
SELECT ROW_NUMBER() OVER (ORDER BY tb17.[NO]) AS OrderId,
* FROM [OFFRES] tb17),
Counters as (SELECT
(select MAX(OrderId) FROM MainQuery ) AS 'TotalRecs',
sum(case when [TypeLoc]=215 then 1 else 0 end) over() Loc215,
sum(case when [TypeLoc]=220 then 1 else 0 end) over() Loc220,
sum(case when [TypeLoc]=526 then 1 else 0 end) over() Loc526,
sum(case when [TypeLoc]=209 then 1 else 0 end) over() Loc209,
sum(case when [TypeLoc]=223 then 1 else 0 end) over() Loc223
/*...*/
from MainQuery
)
SELECT TotalRecs, Loc215, Loc220, Loc526, Loc209, Loc223
FROM Counters
WHERE OrderId BETWEEN 1 AND 20000