mysql 不同查询不起作用

Question

我有一个针对 Drupal 6 的 mysql 查询。但是它并没有按照预期返回不同的 nid。有人可以帮助识别我的代码中的错误吗？

    SELECT DISTINCT( n.nid), pg.group_nid, n.title, n.type, n.created, u.uid, u.name, tn.tid     FROM node n 
INNER JOIN users u on u.uid = n.uid 
LEFT JOIN og_primary_group pg ON pg.nid=n.nid 
LEFT JOIN term_node tn ON tn.vid=n.vid 
WHERE n.nid IN ( 
      SELECT DISTINCT (node.nid) 
      FROM node node 
      INNER JOIN og_ancestry og_ancestry ON node.nid=og_ancestry.nid 
      WHERE og_ancestry.group_nid = 134 ) 
        AND n.status<>0 
        AND n.type NOT IN ('issue') 
        AND tn.tid IN (
             SELECT tid FROM term_data WHERE vid=199 AND ( LOWER(name)=LOWER('Announcement') OR LOWER(name)=LOWER('Report') OR LOWER(name)=LOWER('Newsletter') 
     )) ORDER BY n.created DESC

我可以获得不同 nid 的唯一方法是添加一个 groupby 子句，但这会破坏我的 Drupal 寻呼机查询。

Answer 1

DISTINCT 旨在返回选定的 DISTINCT 行，因此不是将单个列作为 select 子句的一部分，而是返回 ENTIRE select 子句。

SELECT Syntax

The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT.