题目:
自己搜一下。在lecode上。
解题思路:
因为我看到,这里的分组好像和之前用的group by 不太一样,group by 是根据某一栏分组,但是现在,这些栏都不存在。
对不存在的栏(即分组的结果,不在原表格里),应该怎么做?
用case when和sum。
然后union联结起来多个独立的查询结果。
-- 看了一眼答案 用的case when.但是我没写出来。这里的错误在于,没有给category字段赋值,以及 case when 计数写得不对。
select
case when sum(income <20000) as accounts_count then
when then sum(income>=20000 and income<=50000) as accounts_count
else accounts_count
end from Account
-- 仍然不对。这里的错误在于,把case when 和if 搞错了(羞羞)
select 'Low Salary' AS category,sum(case when income <20000 ,1,0) from Account
case when 和sum 的连用。
sum (case when 条件 then 1 else 0 end )
这样可以找到满足条件的数据的个数。
参考答案:
select
'Low Salary' AS category,
sum(case when income <20000 then 1 else 0 end ) as accounts_count
from Accounts
union
select
'Average Salary' AS category,
sum(case when income >=20000 and income <= 50000 then 1 else 0 end ) as accounts_count
from Accounts
union
select
'High Salary' AS category,
sum(case when income >50000 and income then 1 else 0 end ) as accounts_count
from Accounts