MySQL-这种查询如何写，效率如何？

Question

色系表
 色系id   色系名称
 csid    csvalue
 1       红
 2       黑
 
颜色表
 色系id  颜色id  颜色名称
 csid   ysid   ysvalue
 1      1      国旗红
 1      2      明亮红
 2      3      金刚黑
 
服装表（wysid,nysid均为颜色表的ysid值）
 服装id  服装面料颜色 服装里料颜色   
 fzid   wysid      nysid
 1      1          2
 2      2          1
 3      1          3
 
如何通过查询得出以下结果
 服装id  服装面料颜色      服装里料颜色   
 fzid   wysid          nysid
 1      国旗红(红色系)   明亮红(红色系)
 2      明亮红(红色系)   国旗红(红色系)
 3      国旗红(红色系)   金刚黑(黑色系)  

Answer 1

首先建立实验表并插入测试数据，因为MySQL中文有点问题懒得弄了，就全用的英文。不过不影响。

create table sexi (id int not null primary key, name char(50));

create table yanse (id int not null primary key, sexi_id int not null, name char(50));

create table fuzhuang (id int not null primary key, mianliao_yanse_id int not null, liliao_yanse_id int not null);

insert into sexi values(1, 'Red');
insert into sexi values(2, 'Black');
insert into yanse values(1, 1, 'Flag Red');
insert into yanse values(2, 1, 'Bright Red');
insert into yanse values(3, 2, 'Jingang Black');
insert into fuzhuang values (1, 1, 2);
insert into fuzhuang values (2, 2, 1);
insert into fuzhuang values (3, 1, 3);

然后使用下面的SQL查询，要点在于，里料和面料这两列均与sexi和yanse这两个表关联，因此这两个表在查询的时候，需要两个alias，分别与面料和里料这两列进行关联。
在MySQL中，使用concat函数来讲查询结果或者字符串常量进行相加。
使用 as 来指定结果集中的某一列的名称。如果你使用php，在下面的查询情况下，则可以直接用 miaoliao_desc 和 liliao_desc 来获得该列的值。

select fz.id,
concat(yanse1.name, '(', sexi1.name, ')') as miaoliao_desc,
concat(yanse2.name, '(', sexi2.name, ')') as liliao_desc
from
fuzhuang fz,
sexi sexi1, sexi sexi2,
yanse yanse1, yanse yanse2
where fz.mianliao_yanse_id = yanse1.id and yanse1.sexi_id = sexi1.id
and fz.liliao_yanse_id = yanse2.id and yanse2.sexi_id = sexi2.id;

查询结果如下：

+----+-----------------+----------------------+
| id | miaoliao_desc | liliao_desc |
+----+-----------------+----------------------+
| 1 | Flag Red(Red) | Bright Red(Red) |
| 2 | Bright Red(Red) | Flag Red(Red) |
| 3 | Flag Red(Red) | Jingang Black(Black) |
+----+-----------------+----------------------+
3 rows in set (0.00 sec)

使用php获取结果集后，获得咧 miaoliao_desc 和 liliao_desc的代码

if ($row = mysql_fetch_array($result)) {
print $row["miaoliao_desc"]；
print (" ")；
print $row["liliao_desc"]；
print ("< p>")；
}

说到效率，上述sql中，每次查询，与颜色及色系的表进行两两次关联，从理论上讲，可能会降低查询的效率，但是因为从业务上分析，颜色和色系这两个表的记录不会很多，大概会在百到千这个级别，这个级别的数据量，使用上面的sql进行查询是没有问题的。 如果这两个表存放的不是颜色和色系的业务数据，而是可能会有上百万条或者千万条数据的信息，那上面的sql语句需要进行进一步的优化，但是不在本次讨论范围中。

另外如果服装这个表的记录数很多，那可以分别在下面的列建立索引来提高查询效率

mianliao_yanse_id
liliao_yanse_id
sexi.name
yanse.name