☰

选择 Distinct 和 Union 时遇到问题

tag-icon • tag-icon sql
选择 Distinct 和 Union 时遇到问题

我正在尝试对两个表中的年龄范围计数进行求和。我能够将两个表中的总数显示在一个视图中，但无法弄清楚如何删除疏散中心重复项并将每个疏散中心的所有总数放在一行中。

当我向每个表添加 Select Distinct 时出现错误。

SELECT dbo.tblGenPopRegistration_DEV.EvacCenter AS [Evac Center], SUM(CASE WHEN dbo.tblGenPopRegistration_DEV.Age < 6 THEN 1 ELSE 0 END) AS [5 and Under], 
                         SUM(CASE WHEN dbo.tblGenPopRegistration_DEV.Age BETWEEN 6 AND 17 THEN 1 ELSE 0 END) AS [6 to 17], 
             SUM(CASE WHEN dbo.tblGenPopRegistration_DEV.Age > 17 THEN 1 ELSE 0 END) AS [18 and Over]
FROM  dbo.tblGenPopRegistration_DEV
WHERE        (dbo.tblGenPopRegistration_DEV.CheckedIn = 1)
Group by dbo.tblGenPopRegistration_DEV.EvacCenter
UNION ALL
SELECT dbo.tblGenPopAdditionalRegistrations_DEV.EvacCenter AS [Evac Center], SUM(CASE WHEN dbo.tblGenPopAdditionalRegistrations_DEV.Age < 6 THEN 1 ELSE 0 END) AS [5 and Under], 
                         SUM(CASE WHEN dbo.tblGenPopAdditionalRegistrations_DEV.Age BETWEEN 6 AND 17 THEN 1 ELSE 0 END) AS [6 to 17], 
             SUM(CASE WHEN dbo.tblGenPopAdditionalRegistrations_DEV.Age > 17 THEN 1 ELSE 0 END) AS [18 and Over]
FROM dbo.tblGenPopAdditionalRegistrations_DEV
Group by dbo.tblGenPopAdditionalRegistrations_DEV.EvacCenter

答案1

我希望我明白你想要什么。

首先像这样合并两个 SELECT。请注意，我们使用 UNION 而不是 UNION ALL 来匹配问题的第一部分把疏散中心的重复品拿出来。

SELECT dbo.tblGenPopRegistration_DEV.EvacCenter AS [Evac Center], 
       dbo.tblGenPopRegistration_DEV.Age
FROM   dbo.tblGenPopRegistration_DEV
WHERE  (dbo.tblGenPopRegistration_DEV.CheckedIn = 1)
UNION
SELECT dbo.tblGenPopAdditionalRegistrations_DEV.EvacCenter AS [Evac Center], 
       dbo.tblGenPopAdditionalRegistrations_DEV.Age
FROM   dbo.tblGenPopAdditionalRegistrations_DEV

现在使用以下方法进行求和

SELECT EvacCenter AS [Evac Center], 
   SUM(CASE WHEN Age < 6 THEN 1 ELSE 0 END) AS [5 and Under], 
   SUM(CASE WHEN Age BETWEEN 6 AND 17 THEN 1 ELSE 0 END) AS [6 to 17], 
   SUM(CASE WHEN Age > 17 THEN 1 ELSE 0 END) AS [18 and Over]
FROM (
   SELECT dbo.tblGenPopRegistration_DEV.EvacCenter as EvacCenter, 
          dbo.tblGenPopRegistration_DEV.Age as Age
   FROM   dbo.tblGenPopRegistration_DEV
   WHERE  (dbo.tblGenPopRegistration_DEV.CheckedIn = 1)
   UNION
   SELECT dbo.tblGenPopAdditionalRegistrations_DEV.EvacCenter as EvacCenter, 
          dbo.tblGenPopAdditionalRegistrations_DEV.Age as Age
   FROM   dbo.tblGenPopAdditionalRegistrations_DEV
) GROUP BY EvacCenter

你问题的第二部分是将每个疏散中心的所有总数放在一行上。 我不知道我们是否已经得到这个结果。

如果不是，请提供一个示例并提及您的 SQL 数据库或 SQL 方言。我想您想要一个可以通过使用关键字获得的结果

OVER PARTITION BY

相关内容