如何选择所有在mysql中找不到输入数组的数据

Question

CREATE TABLE emp (
empno decimal(4,0) NOT NULL,
ename varchar(10) default NULL,
job varchar(9) default NULL,
mgr decimal(4,0) default NULL,
hiredate date default NULL,
sal decimal(7,2) default NULL,
comm decimal(7,2) default NULL,
deptno decimal(2,0) default NULL
);

INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');






empno   ename    job       mgr         hiredate   sak        comm  depno   
'7369', 'SMITH', 'CLERK',   '7902', '1980-12-17', '800.00', NULL, '20'
'7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30'
'7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30'
'7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', NULL, '20'
'7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30'
'7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', NULL, '30'
'7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', NULL, '10'
'7788', 'SCOTT', 'ANALYST', '7566', '1982-12-09', '3000.00', NULL, '20'
'7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000.00', NULL, '10'

这是我想要的桌子

选择表中找到的数据，但找不到要使用连接或联合的两个数据，我正在尝试使用此查询，但无法执行。

SELECT * FROM (emp)
WHERE  FIND_IN_SET(ename, 'SMITH,WARD,KING,TOM')

Union All
select  * from (emp)  where  !FIND_IN_SET(ename, 'SMITH,WARD,KING,TOM')

我想要输入“ SMITH，WARD，KING，TOM”时

那么它应该像这样重新运行数据：

empno   ename    job       mgr         hiredate   sak        comm  depno  
'7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', NULL, '20'
'7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30'
'7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000.00', NULL, '10'
null   , 'TOM' , null               ,null,      null             null           null, null

请建议我如何实现

我想以这种方式创建“过程”，以便我将传递“ SMITH，ALLEN，TOM”，然后应该以这种方式给3行7369 | SMITH，7499 |
ALLEN，null | TOM

Answer 1

辅助表用于左联接/右联接概念，但并不是那么简单。

从我的答案在这里（Edit3）在这里：

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   |
+-------+

因此，以上是将csv放入查询并从中生成表的一般形式。

现在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 |
+-------------+-------+-------+-----------+------+------------+---------+--------+--------+