MySQL 的复合查询或者嵌套查询

来源：互联网发布：手机淘宝黑屏怎么回事编辑：程序博客网时间：2024/05/23 01:13

MySQL 的复合查询或者嵌套查询，有表两张，要以 clrTheme 表两张为表列，将 clrColor 横向列出，故选择嵌套查询。

create table clrTheme(   clrThemeId           bigint not null auto_increment,   clrGroupId           bigint,   sort                 bigint,   name                 varchar(128),   primary key (clrThemeId));

create table clrColor(   clrColorId           bigint not null auto_increment,   clrThemeId           bigint,   sort                 bigint,   name                 varchar(128),   alias                varchar(128),   C                    decimal(8,5),   M                    decimal(8,5),   Y                    decimal(8,5),   K                    decimal(8,5),   R                    decimal(8,5),   G                    decimal(8,5),   B                    decimal(8,5),   Hex                  varchar(128),   ColorId              varchar(128),   primary key (clrColorId));

MySQL 复合嵌套查询命令如下

select * from clrTheme as t1,(  (select * from clrColor where `sort` = 0) as c1,  (select * from clrColor where `sort` = 1) as c2,  (select * from clrColor where `sort` = 2) as c3,  (select * from clrColor where `sort` = 3) as c4,  (select * from clrColor where `sort` = 4) as c5) where  t1.clrThemeId = c1.clrThemeId  and t1.clrThemeId = c2.clrThemeId  and t1.clrThemeId = c3.clrThemeId  and t1.clrThemeId = c4.clrThemeId  and t1.clrThemeId = c5.clrThemeId  order by t1.clrGroupId, t1.sort asc;

查询结果将以 clrTheme 为主列，将 clrColor 作为子列，根据条件得到结果。

这里针对 clrColor 的内查询建议加条件，以提高性能。

0 0