商品分类如下:
问题1:查询商品中各类饮料,并对饮料的类型进行标注。
程序实现如下:
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实现:
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:查询各类商品的数量
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