辅助表用于左联接/右联接概念，但并不是那么简单。
CREATE TABLE 4kTable
( -- a helper table of about 4k consecutive ints
id int auto_increment primary key,
thing int null
)engine=MyISAM;
insert 4kTable (thing) values (null),(null),(null),(null),(null),(null),(null),(null),(null);
insert 4kTable (thing) select thing from 4kTable;
insert 4kTable (thing) select thing from 4kTable;
insert 4kTable (thing) select thing from 4kTable;
insert 4kTable (thing) select thing from 4kTable;
insert 4kTable (thing) select thing from 4kTable;
insert 4kTable (thing) select thing from 4kTable;
insert 4kTable (thing) select thing from 4kTable;
insert 4kTable (thing) select thing from 4kTable;
insert 4kTable (thing) select thing from 4kTable;
-- verify:
-- select min(id),max(id),count(*) from 4kTable;
-- 1 4608 4608
ALTER TABLE 4kTable ENGINE = InnoDB; -- *********** it is Now InnoDB
从用户fthiella的修改后的答案中 …此处发布
select SUBSTRING_INDEX(SUBSTRING_INDEX(@str, ',', 4k.id), ',', -1) name
from
4kTable 4k
cross join (select @str:='SMITH,WARD,KING,TOM') vars
on CHAR_LENGTH(@str)
-CHAR_LENGTH(REPLACE(@str, ',', ''))>=4k.id-1;
+-------+
| name |
+-------+
| SMITH |
| WARD |
| KING |
| TOM |
+-------+
现在d，根据上述内容创建一个派生表（），并通过RIGHT JOIN与操作码结合（该模式已在操作码中显示）
select d.name as rtable_name,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno
from emp e
right join
( select SUBSTRING_INDEX(SUBSTRING_INDEX(@str, ',', 4k.id), ',', -1) name
from 4kTable 4k
cross join (select @str:='SMITH,WARD,KING,TOM') vars
on CHAR_LENGTH(@str)
-CHAR_LENGTH(REPLACE(@str, ',', ''))>=4k.id-1
) d
on d.name=e.ename;
结果：
+-------------+-------+-------+-----------+------+------------+---------+--------+--------+
| rtable_name | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------------+-------+-------+-----------+------+------------+---------+--------+--------+
| SMITH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| WARD | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| KING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| TOM | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------------+-------+-------+-----------+------+------------+---------+--------+--------+
602392714
清零编程群