sql左联接+一对多关系

Question

我有三个表-节点，content_type_product和share_content。节点和share_content之间可能存在1:N关系。我只想为每个ID提取一条记录。
如果share_content中有多个记录，我想要最新的记录，即sc.auto_id的最大值

SELECT sc.uid, n.uid, n.nid, sc.message 
  FROM node n 
    LEFT JOIN content_type_product p ON n.nid = p.nid 
    LEFT JOIN share_content sc ON n.nid = sc.nid 
  WHERE n.nid = 40513 
  GROUP BY sc.nid
  ORDER BY sc.auto_id

Answer 1

您为什么要加入content_type_product？
但除此之外，请尝试

 SELECT c.uid, n.uid, n.nid, c.message  
 FROM node n  
   LEFT JOIN share_content c 
      ON c.nid = n.nid  
          And c.auto_id
             = (Select Max(auto_id)
                From share_content 
                Where nid = p.nid ) 
 Where n.nid = 40513  
 ORDER BY c.auto_id