mysql - 不要带一排 table

Question

好吧，让我们开始正题吧...

假设我有一个类别(categoyid)“150”，并且不会携带该类别中的任何东西......

原来一篇文章可能属于多个分类，我在分类SELECT中越屏蔽150，它还是会链接到其他分类....

如何才能在 SELECT 中查找类别“150”中的任何项目，即使他也属于“150”之外的另一个类别...

表格:

节点

nodeid
contentid
url
publishdate

节点信息

nodeid
title

节点类别

categoryid
nodeid

文章

contentid
previewimage

======================我尝试过:

SELECT p.nodeid, p.contentid p.publishdate, p.url, c.categoryid, c.nodeid, a.previewimage, a.contentid, e.title FROM `node` AS p 
INNER JOIN `nodecategory` AS c ON p.`nodeid` = c.`nodeid` 
INNER JOIN `article` AS a ON p.`contentid` = a.`contentid` 
INNER JOIN `nodeinfo` AS e ON p.`nodeid` = e.`nodeid` 
WHERE c.`categoryid` 
IN (73,74,77,105,71,70,72,76,100,80,79,78,81,108,145,146,82,142,83,97,153) 
GROUP BY c.nodeid 
ORDER BY p.`publishdate` 
DESC LIMIT 4

Answer 1

我认为你需要一个不存在的子句:

 AND NOT EXISTS (
  SELECT 1
  FROM 
   `nodecategory` AS ic
  WHERE
   p.`nodeid` = ic.`nodeid`
   AND ic.`categoryid` IN (150)
 )

这是您的查询中的内容，稍微重新格式化:

SELECT 
 p.nodeid, 
 p.contentid,
 p.publishdate, 
 p.url, 
 c.categoryid, 
 c.nodeid, 
 a.previewimage, 
 a.contentid, 
 e.title 
FROM 
 `node` AS p 
 INNER JOIN `nodecategory` AS c ON p.`nodeid` = c.`nodeid` 
 INNER JOIN `article` AS a ON p.`contentid` = a.`contentid` 
 INNER JOIN `nodeinfo` AS e ON p.`nodeid` = e.`nodeid` 
WHERE c.`categoryid` 
 IN (73,74,77,105,71,70,72,76,100,80,79,78,81,108,145,146,82,142,83,97,153) 
 AND NOT EXISTS (
  SELECT 1
  FROM 
   `nodecategory` AS ic
  WHERE
   p.`nodeid` = ic.`nodeid`
   AND ic.`categoryid` IN (150)
 )
GROUP BY c.nodeid 
ORDER BY p.`publishdate` 
DESC LIMIT 4

这应该过滤掉属于所选类别之一的节点，但也不属于 not-exists 子句中指定的节点。