php - 具有多个 where 子句的选择查询在一个结果表中返回多个结果行？

Question

我有一个查询，例如:

SELECT COUNT(id) AS user_list FROM tbl_users
WHERE (enrolled = 1 AND age >=15 AND age < 20)
OR (enrolled = 1 AND age >=20 AND age < 25)
OR (enrolled = 1 AND age >=25 AND age < 30)
OR (enrolled = 1 AND age >=30)

如何让它在一行和一个表中为每个 where 条件返回结果:

user_list
---------
   18    --(enrolled = 1 AND age >=15 AND age < 20)
   12    --(enrolled = 1 AND age >=20 AND age < 25)
   22    --(enrolled = 1 AND age >=25 AND age < 30) 
   56    --(enrolled = 1 AND age >=30)

Answer 1

如前所述，您可以使用一个案例并在一个查询中执行所有操作，但是您不需要使用子查询，尽管我不是它的忠实拥护者，MySQL 允许您在分组依据，因此以下将起作用:

SELECT  CASE WHEN (enrolled = 1 AND age >=15 AND age < 20) THEN '15-19'
            WHEN (enrolled = 1 AND age >=20 AND age < 25) THEN '20-24'
            WHEN (enrolled = 1 AND age >=25 AND age < 30) THEN '25-29'
            WHEN (enrolled = 1 AND age >=30) THEN '30+'
            ELSE 'Other'
        END AS AgeRange 
        COUNT(id) AS user_list 
FROM    tbl_users
WHERE   Enrolled = 1
AND     Age >= 15
GROUP BY AgeRange;

请注意，我仍然添加了一个 where 子句以避免扫描冗余数据。

Example on SQL Fiddle

但是，如果您缺少数据，例如将返回一个空表，而不是这个:

AGERANGE    USER_LIST
15-19       0
20-24       0
25-29       0
30+         0

如您所料。为了解决这个问题，我会创建一个伪表以供选择，然后左连接您的数据:

SELECT  t.Name AS Age_Range,
        COUNT(u.ID) AS User_List
FROM    (   SELECT  '15-19' AS Name, 15 AS LowerBound, 20 AS UpperBound, 1 AS Enrolled
            UNION ALL
            SELECT  '20-24' AS Name, 20 AS LowerBound, 25 AS UpperBound, 1 AS Enrolled
            UNION ALL
            SELECT  '25-29' AS Name, 25 AS LowerBound, 30 AS UpperBound, 1 AS Enrolled
            UNION ALL
            SELECT  '30+' AS Name, 30 AS LowerBound, 9999999 AS UpperBound, 1 AS Enrolled
        ) t
        LEFT JOIN tbl_Users u
            ON u.Enrolled = t.enrolled
            AND u.Age >= t.LowerBound
            AND u.Age < t.UpperBound
GROUP BY t.Name;

Example on SQL Fiddle