有意思

下面是用 postgresql 测试的.

声明: 下面的查询语句并没有得到想要的结果

create table z_attr(
aid int not null,
name varchar(30),
title  varchar(30),
type varchar(30)
);
insert into z_attr(aid, name, title, type) values (1, 'height', '高度', 'int'), (2, 'long', '长度', 'tinyint'), (3, 'material', '材质', 'varchar');

create table z_attrl(
id int not null,
aid int not null,
itv int,
vrv varchar(50)
);
insert into z_attrl(id, aid, itv, vrv) values (1, 1, 10, null), (2, 3, null, '金色');

create table z_product(
id int not null,
name varchar(50) not null,
attrs varchar(255)
);
insert into z_product(id, name, attrs) values (1, '盒子', '1,3');

首先, 第一次尝试

select
p.id, p.name, al.itv, al.vrv
from (
select id, name, regexp_split_to_table(attrs, ',')::int as aid from z_product
) as p
left join z_attrl as al on p.aid=al.aid

出来的结果

id           name           aid          itv          vrv
-------------------------------------------------------------
1            盒子           1            10           <null>          
1            盒子           3            <null>       金色   

这个离结果数据已经很接近了, 能通过 aid 来判断 itv 和 vrv 的字段类型, 并且属性值也都能获取.

但是这样的记录有一个很大的问题, 这两条没有办法合并成一条, 如果 group by 的话, 只能是 id name 能合并, 那这样的数据又返回到 product 的出始表数据了, 这个查询就没有任何意义.

遂, 换个方案.

观察表结构以及数据发现, attr 表中的数据类型大致分为两种, 数字和字符, 在 attrl 表中的体现也是两种, 一个 int 一个 varchar, 那么, 数据合并就有希望了, 先将所有 type 是数字的查出来, 然后将字符串的查出来, 一关联就是最终的结果数据了.

就有了如下查询

select t1.id, t1.name, t1.aid, t1.itv, t2.vrv from (
select
p.id, p.name, al.itv, p.aid
from (
select id, name, regexp_split_to_table(attrs, ',')::int as aid from z_product
) as p
left join z_attrl as al on p.aid=al.aid
where p.aid in (1,2)
) as t1
inner join (
select
p.id, al.vrv
from (
select id, regexp_split_to_table(attrs, ',')::int as aid from z_product
) as p
left join z_attrl as al on p.aid=al.aid
where p.aid=3
) as t2 on t1.id=t2.id

结果

id           name           aid          itv          vrv
----------------------------------------------------------------
1            盒子           1            10           金色   

已经和结果数据很接近了, 只差一个别名, 将 itv 换成 height, vrv 换成 material
但是又不能直接换, vrv 字段还好, 因为从给的数据来看 varchar 只有一种类型 aid 是 3.
itv 还得根据 aid 在查一次, 下意识的想到, as 别名查询一次 attr 表

itv as (select title from z_title where aid=p.aid)

然而 sql 是不支持这种写法的.

到这, 单纯的 sql 查询应该不容易实现这种结果了, 如果有请告诉我.

被邀请来的，不过真的不想答。
C表的attr_id=1,3这种格式本身限制了效率；A,B表的type关联也存在问题。反正是我的话不会正面答这个题，直接给优化的表结构和优化理由。

此外对于效率来说，当出现 attr_id=1,3 的时候，如果在SQL中进行字符串处理，那么效率还不如PHP在循环时再分别查询了。

为了逼格而设计这样的表结构是要被揍的

如果是我，表设计大概会是这样子

A表 属性名
aid           name           title
-------------------------------------
1             height          高度
2             long            长度
3             material        材质


B表  属性值

attr_id       aid          attr_value
---------------------------------------
1              1           10
2              1           12
3              2           5
4              3           金色


C表  产品id属性id对应表

pid           attr_id
-------------------------
1              1
1              4


D表 产品表

pid         pname         其他字段
--------------------------------------
1           iphone7
2           鞋子

这个直接用sql应该不能完成吧，存储过程差不多能搞出来。

复杂需求，用存储过程 + 临时表比较合适。

PHP多条sql语句也行，但你说要考虑效率，这种方法就不合适。

为什么按照PHP的思维就要再循环查询？咱不可以把都先把想要的数据都根据条件取出来之后，然后再根据ID去匹配取出数据么？此外，属性值的那个表是不是差一个商品id？不然怎么知道这个属性值是具体哪个商品的？说一下我这个的思路，首先根据商品id把属性id取出来，然后再属性值表中根据商品id和属性id取出来他的商品属性值,根据属性id取出他的属性名（当然，这里可以连表取出来，但是一般我都是单条sql取数据，毕竟数据量大了之后连表还是效率低一些的），接着分别遍历后面的两个表，转化成属性id=>属性名和属性值的形式（如果是连表的，就没有这一步），再和最开始的商品那个根据商品id匹配即可

这样的表结构。。