代码之家 › 专栏 › 技术社区 › unrelativity

左连接上的MySQL限制

mysql sql

unrelativity · 技术社区 · 15 年前

我的问题是:

   SELECT issues.*, 
          comments.author AS commentauthor, 
          comments.when_posted AS commentposted
     FROM issues
LEFT JOIN (SELECT * 
             FROM comments 
         ORDER BY when_posted DESC 
            LIMIT 1) AS comments ON issues.id=comments.issue
ORDER BY IFNULL(commentposted, issues.when_opened) DESC

如果我从那里删除“限制1”部分,我会得到一个问题中每个评论的一行,而这不是我想要的。我想要的只是每个问题的最新评论。

3 回复 | 直到 15 年前

manji 15 年前

   SELECT issues.*, 
          comments.author AS commentauthor, 
          comments.when_posted AS commentposted
     FROM issues
LEFT JOIN ( SELECT c1.issue, c1.author, c1.when_posted
              FROM comments c1
           JOIN
           (SELECT c2.issue, max(c2.when_posted) AS max_when_posted           
              FROM comments c2
          GROUP BY issue) c3
            on c1.issue = c3.issue and c1.when_posted = c3.max_when_posted
          ) AS comments ON issues.id=comments.issue
 ORDER BY COALESCE(commentposted, issues.when_opened) DESC

OMG Ponies 15 年前

尝试:

   SELECT i.*,
          c.author AS commentauthor,
          c.when_posted AS commentposted
     FROM ISSUES i
LEFT JOIN COMMENTS c ON c.issue = i.id
     JOIN (SELECT c.issue,
                  MAX(c.when_posted) 'max_when_posted'
             FROM COMMENTS c
         GROUP BY c.issue) mwp ON mwp.issue = c.issue
                              AND mwp.max_when_posted = c.when_posted
 ORDER BY COALESCE(c.when_posted, i.when_opened) DESC

Joel Coehoorn 15 年前

编辑

由于MySql毕竟没有CTE,请尝试以下方法:

SELECT i.*
    c.author AS CommentAuthor,
    c.when_posted AS CommentPosted
FROM Issues i
LEFT JOIN 
    (SELECT issue, MAX(when_posted) AS LastPostDate 
     FROM comments GROUP BY issue) ic ON ic.issue = i.id
LEFT JOIN Comment c ON c.issue = i.id AND c.when_posted = ic.LastPostDate  
ORDER BY COALESCE(ic.LastPostDate, issues.when_opened) DESC

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

4 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

4 月前

John D · 需要为NULL或NOT NULL的WHERE子句

4 月前

ojek · 如何对SQL结果进行分组和编号?

4 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

4 月前

Sax · 规范化Google表格(第一步)

4 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

4 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

4 月前

Andrus · 如何在sql中查找第二个匹配项

5 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

5 月前