access:
select a.账单编号, sum(iif(b.商品类型='烟类', c.数量 * c.价格,0)) as 烟类,
sum(iif(b.商品类型='烟类', c.数量 * c.价格,0)) as 酒类,
sum(iif(b.商品类型='烟类', c.数量 * c.价格,0)) as 茶类
from 消费表 c inner join 账单表 a on a.账单编号=c.账单编号
inner join 价格表 b on b.商品名称=c.商品名称
where a.账单编号='20030101001'
group by a.账单编号
select a.账单编号, sum(case when b.商品类型='烟类' then c.数量 * c.价格 else 0 end) as 烟类,
sum(case when b.商品类型='酒类' then c.数量 * c.价格 else 0 end) as 酒类,
sum(case when b.商品类型='茶类' then c.数量 * c.价格 else 0 end) as 茶类
from 消费表 c inner join 账单表 a on a.账单编号=c.账单编号
inner join 价格表 b on b.商品名称=c.商品名称
where a.账单编号='20030101001'
group by a.账单编号