SQL query ALL in One -避免查询次数

Question

我一直在尝试根据不同月份的年龄差异生成报告，目前我知道的解决方案是为每个月和每个年龄差异分别进行单独的sql查询，这导致每个月创建4个不同的sql查询。请告诉我是否可以为每个月创建一个sql查询，如下所示

如果要为JAN生成年龄差异为40岁以上、50 - 60岁、60-70岁和80岁以上的报告

如何将其放在一个sql查询中并从中获得四个不同的结果

类似的东西(只是一个想法)

Select count(Above 40),count(In Between 50 - 60),count (In B/W 60-70), count(above 80) from users

Answer 1

我将IF()和SUM()一起使用，如下所示：

SELECT SUM(IF(age >= 40,1,0))               AS older40, 
       SUM(IF(age >= 50 and age <= 60,1,0)) AS between50and60
       SUM(IF(age >= 60 and age <= 70,1,0)) AS between60and70
       SUM(IF(age >= 80,1,0))               AS over80

FROM TABLE

参考MySQL对IF(expr1,expr2,expr3)的解释，但基本上expr1是条件，expr2是条件为真时的值，expr3是else时的值。使用1&0可以让SUM()对记录进行计数。

Answer 2

试试像这样的东西

Select Range,Count(*) From
(Select 
Case 
When (Age >= 40 and Age < 50) Then 'Above40'
When (Age >= 50 and Age < 60) Then 'Between 50 - 60'
When (Age >= 60 and Age < 70) Then 'In B/W 60-70'
When (Age > 80) Then 'Above 80'
else 'Not Counted'
end as Range
From Users) as Ranges
Group By Range

猜测看守，因为你似乎有一些差距和重叠在他们。

Answer 3

select *
from (
(select COUNT(Age) as `c1`, '40-50' AS 'freq' from `Users` WHERE Age BETWEEN 40 AND 50)
UNION ALL
(select COUNT(Age)  as `c2`, '50-60' AS 'freq' from `Users` WHERE Age BETWEEN 50 AND 60)
UNION ALL
(select COUNT(Age)  as `c2`, '60-70' AS 'freq' from `Users` WHERE Age BETWEEN 60 AND 70)
) as aliasquery ;