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

2018-01-25 10:18 来源:未知 作者:mas 责编:mas
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 的内查询建议加条件,以提高性能。

【版权声明】本文内容来源于互联网,本站不拥有所有权,不承担相关法律责任。如果发现本站有侵权的内容,欢迎发送邮件至 benumon@163.com 举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。

分享:

更多文章

相关文章

关键词:

一生受益-思韵闪耀-专业IT技术社区 - 爱编程,爱源码,爱技术,一生受益。

powerby 一生受益-思韵闪耀    豫ICP备13002912号-2