商品分類如下：
問題1：查詢商品中各類飲料,并對(duì)飲料的類型進(jìn)行標(biāo)注。
程序?qū)崿F(xiàn)如下：
if(id=="b")
{
a++;
}
else if(id=="d")
{
d++;
}
else if(id=="c")
{
if(subid=="e")
{
e++;
}
else if(subid=="f")
{
f++;
}
else if(subid=="g")
{
g++;
}
}
sql實(shí)現(xiàn)：
select (case when id='b' then 'B'
when id='d' then 'D'
when id='c' then decode(subid,'e','E','f','F','g','G','Ohter')
else null end) type
from tab
問題2：查詢各類商品的數(shù)量
select
count(case when a.id='b'then 1 else null end) as B,
count(case when a.id='d'then 1 else null end) as D,
count(case when a.id='c' and subid='e' then 1 else null end) as E,
count(case when a.id='c' and subid='f' then 1 else null end) as F,
count(case when a.id='d' and subid='g' then 1 else null end) as G
from atab a