如何在表上选择并计算某些值的出现次数

我请求你的帮助，因为我不太了解 SQL。

我需要计算表列中某些值的出现次数，以达到类似统计表的效果，如下图所示：

需要的结果：

Comment:

我的结果表需要有前两列（国家和站点）来自第一个表“Violations”，接下来的 5 列将包含状态表中每个可能的 id 值中“Violations”中的 status_id 出现次数（计数）。

解释：

因此，我有两个现有表：“违规”和“状态”。请看我的sqlfiddle http://www.sqlfiddle.com/#!2/fb466/1/0

违规行为：

• id long,
• 国家 varchar(20),
• 站点 varchar(20),
• status_id long,
• ...在这种情况下其他列并不重要

Status:

• id long,
• 状态长 “状态”列的值 (1-4) 映射到字符串值：可疑违规 (1)、已确认违规 (2)、已确认无违规 (3)、未确定 (4)

我的连接结果（或仅基于一个表违规）是拥有应包含列的表：

• 来自违规表：“国家/地区”和“站点”
• 来自状态表：“可疑违规”、“已确认违规”、“已确认无违规”、“未确定”、“总计”（其中此列是违规表中发生次数的计数器）。

当前状态和新要求：

第一次尝试在下面完成（感谢 bluefeet）并且几乎是完美的......

select v.country,
v.site,
    SUM(case when s.id = 1 then 1 else 0 end) Total_SuspectedViolations,
    SUM(case when s.id = 2 then 1 else 0 end) Total_ConfirmedViolations,
    SUM(case when s.id = 3 then 1 else 0 end) Total_ConfirmedNoViolations,
    SUM(case when s.id = 4 then 1 else 0 end) Total_NotDetermined,
    COUNT(*) Total
from violations v
inner join status s
    on v.status_id = s.id
group by v.country, v.site

或没有加入：

select v.country,
v.site,
    SUM(case when v.status_id = 1 then 1 else 0 end) Total_SuspectedViolations,
    SUM(case when v.status_id = 2 then 1 else 0 end) Total_ConfirmedViolations,
    SUM(case when v.status_id = 3 then 1 else 0 end) Total_ConfirmedNoViolations,
    SUM(case when v.status_id = 4 then 1 else 0 end) Total_NotDetermined,
    COUNT(*) Total
from violations v
group by v.country, v.site

...但不包括 3 个问题，正如您在图片中看到的那样。我是说：

• “- All -”应该计算所有国家/地区的出现次数
• “- 未知 -” 应该计算一些不被认可的国家/地区的出现次数
• “- All -”（关于每个国家/地区）- 应计算一个国家/地区内的出现次数

附加说明：

• -Unknown-意义：

未知应该计算国家/地区的出现次数，例如数据库国家/地区表中不存在或名称/ID 错误的国家/地区，这就是为什么此处将其视为Unknown（我忘了提到有桌子Country在数据库中）。 对于网站来说也是如此，Unknown对于站点意味着有人在 Violations.status_id 中输入了错误的值，而不是范围 (1-4)，因为这些值只是状态表中存在的可接受的值。

• 我们可以假设表 Country 如下所示：

Country:

• id long,
• 名称 varchar(30)

请帮助我编写正确的sql查询，其中包括这3个条件，因为我有一个很大的问题要做。

The All案例可以使用轻松完成UNION声明（见sqlFiddle http://www.sqlfiddle.com/#!2/fb466/23对于结果）：

(SELECT v.country,
    v.site,
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
    0  'isAll'
FROM violations v
GROUP BY v.country, v.site)
union(
SELECT v.country,
    '- All -',
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
  1  'isAll'
FROM violations v
GROUP BY v.country)
UNION (
SELECT '- All -',
    '- All -',
    SUM(CASE WHEN v.status_id = 1 THEN 1 ELSE 0 END) Total_SuspectedViolations,
    SUM(CASE WHEN v.status_id = 2 THEN 1 ELSE 0 END) Total_ConfirmedViolations,
    SUM(CASE WHEN v.status_id = 3 THEN 1 ELSE 0 END) Total_ConfirmedNoViolations,
    SUM(CASE WHEN v.status_id = 4 THEN 1 ELSE 0 END) Total_NotDetermined,
    COUNT(*) Total,
  1  'isAll'
FROM violations v)
ORDER BY country, isAll DESC, site

然而，这种查询的性能可能并不是很好，所以我并不是说这是最好的解决方案 - 但它确实有效。

带有“未知”的版本