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

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 的内查询建议加条件，以提高性能。