PHP mysql嵌套查询"where not in" - php mysql nested query "where not in"

php mysql nested query "where not in"

我有两个表test_category和results:

CREATE TABLE IF NOT EXISTS `test_category` (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `score_type` varchar(50) NOT NULL,
  `sub_code` int(11) NOT NULL,
  `duration` varchar(10) NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `sub_code` (`sub_code`)
)
CREATE TABLE IF NOT EXISTS `results` (
  `res_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `score_type` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  `date_taken` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`res_id`),
  KEY `user_id` (`user_id`),
  KEY `score_type` (`score_type`)
)

我想选择一些存在于test_category但不存在于results.

这里是SQL我已经尝试过，但它不工作:

select _id, score_type from test_category where _id in ('13') and not in(select score_type from results where user_id=349);

您在AND

后面漏写了列名

试试这个,

SELECT _id, score_type FROM test_category WHERE _id IN
('13') AND _id NOT IN(SELECT score_type FROM results WHERE user_id=349);

你的查询应该是这样的:

select _id, score_type 
    FROM test_category 
      WHERE _id in ('13') 
       AND _id NOT IN (select score_type 
                         FROM results 
                           WHERE user_id=349);