mysql - 如何修改这个查询？

Question

我有一个包含重要列的数据库表，如下所示:

name | violation
file1| major
file1| major
file1| minor
file2| minor
file2| minor

我想要得到这样的结果:

name | minor | major etc.
file1| 1     | 2
file2| 2     | 0

我已经通过这个查询实现了这一点:

select distinct component as cmp ,project_id,
(select count(severity) from issue_lines where severity = 'MINOR' AND component = cmp) as minor,
(select count(severity) from issue_lines where severity = 'MAJOR' AND component = cmp) as major,
(select count(severity) from issue_lines where severity = 'CRITICAL' AND component = cmp) as critical,
(select count(severity) from issue_lines where severity = 'INFO' AND component = cmp) as info,
(select count(severity) from issue_lines where severity = 'BLOCKER' AND component = cmp ) as blocker
from issue_lines

现在我想让它进入 View ，因为它会被多次重复使用。但是厚颜无耻的 ActiveRecordexecute() 命令将子查询中的 cmp 解析为组件，最后我得到组件==组件，它返回所有文件的总体违规，而不是单个文件。

我可以直接通过 Workbench 创建此 View ，没有任何问题，但这不是一个选项。

是否可以修改我的查询以避免与execute()命令发生冲突？

Answer 1

使用上面的示例数据，您也许可以使用下面的方法重写您的查询。

SELECT  v.name,
        COUNT(CASE WHEN v.violation = 'minor' THEN 1 END) as minor,
        COUNT(CASE WHEN v.violation = 'major' THEN 1 END) as major
FROM    violations as v
GROUP BY v.name;

SQL Fiddle