sql_join_case when示例_统计金额

sql_join_case when示例_统计金额

统计每个time下每个type的金额

select 
    time,
    type
    sum(price)
from purchase a 
 join
    purchase_commodity b
where a.user=b.user    
group by time,type

统计食品（饮料，盒饭，面包）和非食品（化妆品）的结账金额

select 
     flag,
     sum(payment)
from     
     (
    select
    case  b.type
    when '化妆品' then '1'
    else '0' end flag,
    payment
from purchase a 
 join
    purchase_commodity b
where a.user=b.user
     )
group by flag;

请写出查询代码，统计每5岁为一个年龄层，每个年龄层的人数（20岁到39岁的会员分开每5岁为一个年龄层，其他年龄分类到‘其他年龄’）

select 
    flag,
    count(1)
    (
    select 
    CASE WHEN age >=20 and age <25 THEN '1'
    CASE WHEN age >=25 and age <30 THEN '2'
    CASE WHEN age >=30 and age <35 THEN '3'
    CASE WHEN age >=35 and age <40 THEN '4'
    ELSE '其他年龄' END flag 
from 
(
 --获取年龄   
to_char(to_date(sysdate,'yyyy'))-substr(birthday,1,4) as age
from t_user    
)