我需要计算表格列中的某些值,以实现像统计表一样的效果,如下图所示：
需要的结果：
评论：
我的结果表需要有前两列(contry和site)来自第一个表“Violations”和接下来的5个列,这些列将包含Status表中每个可能的id值的“Violations”中出现的status_id数量(count).
说明：
所以,我有两个表：违规和状态.请看我的sqlfiddle
违规行为：
> id很长,
> country varchar(20),
> site varchar(20),
> status_id long,< - 这是状态表中状态的ID.
> …在这种情况下不重要的其他列
状态：
> id很长,
>状态长
列“status”具有值(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,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个问题,如图所示.我的意思是：
>“ – 全部 – ”应计算所有国家/地区的出现次数
>“ – 未知 – ”应计算某些未被识别国家/地区的出现次数
>“ – 全部 – ”(关于每个国家) – 应计算一个国家内的事件
附加说明：
> -Unknown-含义：
未知应该计算例如在DB Country表中不存在或者名称/ ID错误的国家/地区的出现次数,这就是为什么在这里被视为未知(我忘了提到DB中有表Country).
对于站点而言相同,站点未知意味着有人在Violations.status_id中输入的值不是来自范围(1-4),因为这些只是状态表中存在的可接受值.
>我们可以假设表国家看起来像：
国家：
> id很长,
>名称varchar(30)
解决方法
(SELECT v.country,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 -',1 'isAll' FROM violations v GROUP BY v.country) UNION ( SELECT '- All -',1 'isAll' FROM violations v) ORDER BY country,isAll DESC,site
但是,对于那种查询,性能可能不是很好,所以我不是说这是最好的解决方案 – 但它确实有效.
带有’Unknow’的版本
http://www.sqlfiddle.com/#!2/abfb7/21
(SELECT IF(c.name IS NULL,'- Unknow -',c.name) as name,0 'isAll' FROM violations v LEFT JOIN country c ON c.name = v.country GROUP BY c.name,v.site) union( SELECT IF(c.name IS NULL,1 'isAll' FROM violations v LEFT JOIN country c ON c.name = v.country GROUP BY c.name) UNION ( SELECT '- All -',1 'isAll' FROM violations v LEFT JOIN country c ON c.name = v.country) ORDER BY name,site
