select b.province_name,
(case
when c.type_id=101 then set c.type_name="钻石卡"
when c.type_id=102 then set c.type_name="金卡"
when c.type_id=103 then set c.type_name= "银卡"
else set type_name= "普通卡"
end case) type_name
sum(a.points_earned) history_earned,sum(a.points_balance) points_balance
from ecc_bj.ecc_points a,ecc_bj.ecc_sv_province b,ecc_bj.ecc_account_grade c
where a.account_grade=c.type_id and substr(a.customer_id,1,2)=b.province_id
group by (case
when c.type_id=101 then set c.type_name="钻石卡"
when c.type_id=102 then set c.type_name="金卡"
when c.type_id=103 then set c.type_name= "银卡"
else set type_name= "普通卡"
end case),b.province_name
(case
when c.type_id=101 then set c.type_name="钻石卡"
when c.type_id=102 then set c.type_name="金卡"
when c.type_id=103 then set c.type_name= "银卡"
else set type_name= "普通卡"
end case) type_name
sum(a.points_earned) history_earned,sum(a.points_balance) points_balance
from ecc_bj.ecc_points a,ecc_bj.ecc_sv_province b,ecc_bj.ecc_account_grade c
where a.account_grade=c.type_id and substr(a.customer_id,1,2)=b.province_id
group by (case
when c.type_id=101 then set c.type_name="钻石卡"
when c.type_id=102 then set c.type_name="金卡"
when c.type_id=103 then set c.type_name= "银卡"
else set type_name= "普通卡"
end case),b.province_name
(case
when c.type_id=101 then "钻石卡"
when c.type_id=102 then "金卡"
when c.type_id=103 then "银卡"
else "普通卡"
end) type_name
sum(a.points_earned) history_earned,sum(a.points_balance) points_balance
from ecc_bj.ecc_points a,ecc_bj.ecc_sv_province b,ecc_bj.ecc_account_grade c
where a.account_grade=c.type_id and substr(a.customer_id,1,2)=b.province_id
group by c.type_name,b.province_name
(case c.type_id
when 101 then '钻石卡'
when 102 then '金卡'
when 103 then '银卡'
else '普通卡'
end ) type_name,
sum(a.points_earned) history_earned,sum(a.points_balance) points_balance
from ecc_bj.ecc_points a,ecc_bj.ecc_sv_province b,ecc_bj.ecc_account_grade c
where a.account_grade=c.type_id and substr(a.customer_id,1,2)=b.province_id
group by (case c.type_id
when 101 then '钻石卡'
when 102 then '金卡'
when 103 then '银卡'
else '普通卡'
end ),b.province_name
(case
when c.type_id=101 then "钻石卡"
when c.type_id=102 then "金卡"
when c.type_id=103 then "银卡"
else "普通卡"
end case) type_name
sum(a.points_earned) history_earned,sum(a.points_balance) points_balance
from ecc_bj.ecc_points a,ecc_bj.ecc_sv_province b,ecc_bj.ecc_account_grade c
where a.account_grade=c.type_id and substr(a.customer_id,1,2)=b.province_id
group by c.type_name,b.province_name
(case c.type_id
when 101 then '钻石卡'
when 102 then '金卡'
when 103 then '银卡'
else '普通卡'
end ) type_name,
sum(a.points_earned) history_earned,sum(a.points_balance) points_balance
from ecc_bj.ecc_points a,ecc_bj.ecc_sv_province b,ecc_bj.ecc_account_grade c
where a.account_grade=c.type_id and substr(a.customer_id,1,2)=b.province_id
group by c.type_id,b.province_name
(case c.type_id
when 101 then '钻石卡'
when 102 then '金卡'
when 103 then '银卡'
else '普通卡'
end ) type_name
sum(a.points_earned) history_earned,
sum(a.points_balance) points_balance
from ecc_bj.ecc_points a,ecc_bj.ecc_sv_province b,ecc_bj.ecc_account_grade c
where a.account_grade=c.type_id and substr(a.customer_id,1,2)=b.province_id
group by c.type_id,
b.province_name==
CASE WHEN C1 THEN R1 WHEN C2 THEN R2 . . . WHEN CN THEN RN ELSE RDEND
==
In the syntax definition, C1, C2 . . . Cn represent conditions, and R1, R2 . . . RN represent results.
CASE E0 WHEN E1 THEN R1 WHEN E2 THEN R2 . . . WHEN EN THEN RN ELSE RDEND
Each of the expressions E1...EN are compared to expression E0
以上两种写法都可以