mysql问题:取另一个表中不存在的数据

发布网友发布时间：2022-04-10 04:48

共1个回答

热心网友时间：2022-04-10 06:17

CREATE TABLE person( personid  INT);
INSERT INTO person VALUES(1);
INSERT INTO person VALUES(2);
INSERT INTO person VALUES(3);

CREATE TABLE `read`( readid  char(1),  personid  INT);
INSERT INTO `read` VALUES ('a',1);
INSERT INTO `read` VALUES ('a',2);
INSERT INTO `read` VALUES ('b',2);
INSERT INTO `read` VALUES ('c',3);

SELECT
  allData.*
FROM
(
SELECT
  DISTINCT
  person.personid,
  `read`.readid
FROM
  person  cross JOIN  `read`
)  allData
  LEFT  JOIN  `read`  R
  ON  (allData.personid =  R.personid  AND  allData.readid =  R.readid)
WHERE
  R.personid  IS  NULL;


+----------+--------+
| personid | readid |
+----------+--------+
|        3 | a      |
|        1 | b      |
|        3 | b      |
|        1 | c      |
|        2 | c      |
+----------+--------+
5 rows in set (0.00 sec)