Question

我有两点询问,每一份 no子清单都交回。

SELECT node.nid 
  FROM dpf_node AS node 
 WHERE node.type =  image  AND node.nid; 

SELECT node.nid 
  FROM dpf_node AS node, dpf_image_galleries_images AS image 
 WHERE image.image_nid = node.nid  
   AND node.type =  image 
   AND image.gallery_nid = 138;

这两项工作都是正确的。

归根结底,尽管我想要获得第一个结果清单而不是第二个结果清单中的女婴清单,但我一直在利用这一询问:

SELECT node.nid 
  FROM dpf_node AS node 
 WHERE node.type =  image  
   AND node.nid NOT IN (SELECT node.nid 
                          FROM dpf_node AS node, dpf_image_galleries_images AS image 
                         WHERE image.image_nid = node.nid 
                           AND node.type =  image  
                           AND image.gallery_nid = 138);

而此时此刻,这只是为了打上了两条ball子,使ache子停止。我怀疑(hope)清除数据并再次着手解决数据问题,但真的希望在系统一旦出现后重新确定其头部的情况下解决实际问题。

Answer 1

改为NOT EXISTS。询问如何帮助改进业绩:

SELECT node.nid 
  FROM dpf_node AS node 
 WHERE node.type =  image 
       AND NOT EXISTS (
         SELECT 1
         FROM dpf_node AS i_node
         JOIN dpf_image_galleries_images AS image ON i_node.nid = image.image_nod
         WHERE node.type =  image 
         AND image.gallery_nid = 138
         AND i_node.nid = node.nid
       )

您还应核实您是否有适当的指数。

Answer 2

MySQL might be doing rather silly stuff with correlated subqueries (look at the query plan). If you just want things from the first query that are not in the second, you might get significantly better performance from a LEFT OUTER JOIN of the two, with a filter condition that filters out rows that have nulls for the second set of results.

Answer 3

你们应该能够用一种选择来做到这一点。不要选择你不希望的那点,这样你就可以排除它们,而是首先选择你想要的节点。如果图像画廊和节点之间能够存在许多关系,那么你只希望有独特的节点。这样,它就成为一种独特的。

SELECT node.nid
FROM dpf_node AS node
JOIN dpf_image_galleries_images AS image 
    ON node.nid = image.image_nod
WHERE node.type =  image 
AND image.gallery_nid != 138

取决于贵国数据是如何提供的,以及数据有多少,这应比条款的分顺序要好得多。更便于理解。

友情链接