史上最难的mysql面试题啊,列C的产品,求得它的属性标识和值

Question

要考虑效率,比如查询一千个商品,若按照php思维先列产品再循环根据条件又查询,那么将是个很低很低的效率,总而言之能mysql一句就不能两句有的人还会说设计逻辑什么的,拜托只是一道面试题,低头求解即可,别想太复杂.

题目如下:ABC表求得D结果,即用最精简的mysql列出C的产品及属性标识和值

为何被踩呢,这种题目挺考技术啊.踩的同学来说下原因呗

Answer 1

有意思

下面是用 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                     
1            盒子           3                   金色

这个离结果数据已经很接近了, 能通过 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 查询应该不容易实现这种结果了, 如果有请告诉我.