多次使用 left join 左连接 多个表

1：product_version 2： p_category 3：p_category_product

引用

select
  pc.name as "产品类别",
  pv.product_id "产品ID",
  pv.version_id as "版本ID",
  pv.name as "产品名称",
  pv.price as "原价",
  pv.curr_price "现价",
  pv.onsale_time as "上架时间",
  decode(pv.cost_type,1,'按次',2,'包时段',3,'按课时',4,'免费') as "计费类型",
  case
    when pv.cost_type=2 and pv.useful_life_type=1 then '有效天数'
    when pv.cost_type=2 and pv.useful_life_type=2 then '截止日期'
  end as "有效期类型",
  pv.end_date as "过期时间"
from product_version pv
left join p_category_product pcp on pcp.product_id=pv.product_id
left join p_category pc on pc.id=pcp.p_category_id
where pv.sale_on_class=1 and pv.status=1
order by 2 desc;

评论

1 楼 liuna2011 2011-12-15  

ps:Oracle中CASE WHEN的用法实例,可用于分组分段统计
http://blog.csdn.net/rznice/article/details/6772000