MySQL 的复合查询或者嵌套查询_mysql_数据库_思韵闪耀

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

思韵闪耀

2018-01-25

0次

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

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

create table tmpb  
(  
   tmpbId           bigint not null auto_increment,  
   tmpaId           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 (tmpbId)  
);

MySQL 复合嵌套查询命令如下

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

查询结果将以 tmpa 为主列，将 tmpb 作为子列，根据条件得到结果。
这里针对 tmpb 的内查询建议加条件，以提高性能。

mysql

上一篇：mysql中select into 和sql中的select into 对比

下一篇：MySQL实现删除数据左右空格

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

相关内容

热门资讯