如何使用嵌套大小写并将结果组合在一起。这是我的查询:
SELECT COUNT(inc.inc_id) AS event_count,
CASE inc_data.event_type
WHEN 'c' then case inc_data.sub_event_type
when 's' then 'SR' else 'Project'
end
WHEN 'i' then 'incident'
WHEN 'p' then 'Problem'
WHEN 'd' then 'Decision'
WHEN 't' then 'Task'
end "event_sub_type"
FROM inc INNER JOIN inc_data ON inc.inc_id = inc_data.inc_id
GROUP BY inc_data.event_type, inc_data.sub_event_type
返回:
+-------------+----------------+
| event_count | event_sub_type |
+-------------+----------------+
| 5 | Project |
| 10 | Decision |
| 15 | Incident |
| 20 | Problem |
| 25 | Task |
| 30 | SR |
+-------------+----------------+
预期产出:
+-------------+----------------+
| event_count | event_sub_type |
+-------------+----------------+
| 5 | Project |
| 25 | Others |
+-------------+----------------+
如何修改上述查询以获得预期的输出?
你能试试这个吗?
SELECT COUNT(inc.inc_id) AS event_count,
(CASE WHEN (inc_data.event_type = 'c' AND inc_data.sub_event_type <> 's') THEN 'Project' ELSE 'Others' END ) "event_sub_type"
FROM inc INNER JOIN
inc_data ON inc.inc_id = inc_data.inc_id
GROUP BY (CASE WHEN (inc_data.event_type = 'c' AND inc_data.sub_event_type <> 's') THEN 'Project' ELSE 'Others' END )
怎么样
SELECT COUNT(inc.inc_id) AS event_count,
CASE inc_data.event_type
WHEN 'c' then case inc_data.sub_event_type
when 's' then 'Other' else 'Project'
end
ELSE 'Project'
END "event_sub_type"
FROM inc INNER JOIN inc_data ON inc.inc_id = inc_data.inc_id
GROUP BY inc_data.event_type, inc_data.sub_event_type
根据您的输出,我假设您使用的是MySQL。
MySQL 允许您按列号分组,因此您可以将 GROUP BY 子句替换为以下内容:
GROUP BY 1, 2