mysql查询

多表连接查询

有段时间没有编程，再次写个SQL语句都有点困难了，错误重重的，好记性不如烂笔头，记个博客吧！

有这么三个表

商品类型表--goods_type

商品属性表 -- attribute

商品属性分组表

【需求】做一个商品类型列表页面，需要展示商品类型名称、属性分组、属性个数等信息，如下图

【第一次写的sql】

select a.goods_type_id,a.goods_type_name,a.is_show,a.sort_oder,b.attr_num,d.attr_group_num from
(select goods_type_id,goods_type_name,is_show,sort_oder from goods_type where is_show=1) as a
left join
(select count(*) as attr_num,goods_type_id from attribute where is_show=1 group by goods_type_id) as b
left join
(select count(*) as attr_group_num,c.goods_type_id from (select attr_group_id,goods_type_id from attribute where 
is_show=1 group by attr_group_id) as c group by c.goods_type_id ) as d
on d.goods_type_id = a.goods_type_id and b.goods_type_id = a.goods_type_id

结果执行后，总是提示

出现错误之后，我把sql语句拆分成三条语句分别执行，结果都是ok的，错到哪里了，尼玛，sql语句中哪有一个引号的，好是郁闷啊，后来在网上查了一下，原来关联查询的条件位置写错了

select a.goods_type_id,a.goods_type_name,a.is_show,a.sort_order,b.attr_num,d.attr_group_num from
(select goods_type_id,goods_type_name,is_show,sort_order from goods_type where is_show=1) as a
left join 
(select count(*) as attr_num,goods_type_id from attribute where is_show=1 group by goods_type_id) as b
on b.goods_type_id = a.goods_type_id 
left join
(select count(*) as attr_group_num,c.goods_type_id from (select attr_group_id,goods_type_id from attribute where 
is_show=1 group by attr_group_id) as c group by c.goods_type_id ) as d
on d.goods_type_id = a.goods_type_id