多表连接查询
有段时间没有编程,再次写个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