4个查询在同一个表上，一个更好的方法?

Currently I am doing one query, with 3 sub queries,
all queries are on the same table,
all queries have different where clauses

目前我正在执行一个查询，有3个子查询，所有查询都在同一个表上，所有查询都有不同的where子句

I have thought about doing a group by however this would destroy the SUM()

我曾经想过做一个组但是这会破坏SUM()

here is the query

这是查询

SELECT SUM(club) AS club, 
(SELECT COUNT(id) FROM action_6_members WHERE SUBSTR(CODE, 1, 1) = '9') AS 5pts,
(SELECT COUNT(id) FROM action_6_members WHERE SUBSTR(CODE, 1, 1) = 'A') AS 10pts,
(SELECT COUNT(id) FROM action_6_members WHERE SUBSTR(CODE, 1, 1) NOT IN('9', 'A')) AS General
FROM action_6_members;

here is the explain

这是解释

id  select_type  table             type  rows  Extra        
1   PRIMARY      action_6_members  ALL   1471               
4   SUBQUERY     action_6_members  ALL   1471  Using where  
3   SUBQUERY     action_6_members  ALL   1471  Using where  
2   SUBQUERY     action_6_members  ALL   1471  Using where  

2 个解决方案

#1

Use:

使用:

SELECT SUM(club) AS club, 
       SUM(CASE WHEN SUBSTR(CODE, 1, 1) = '9' THEN 1 ELSE 0 END) AS 5pts,
       SUM(CASE WHEN SUBSTR(CODE, 1, 1) = 'A' THEN 1 ELSE 0 END) AS 10pts,
       SUM(CASE WHEN SUBSTR(CODE, 1, 1) NOT IN('9', 'A') THEN 1 ELSE 0 END) AS General
  FROM action_6_members;

#2

Tweak this:

调整:

SELECT SUM(club) as club, COUNT(1) FROM action_6_members GROUP BY SUBSTR(CODE, 1, 1)