Oracle选择评论最多的新闻

Question

我有两张桌子：news和comments。

News表：

CREATE TABLE "NEWS"
(
  "NEWS_ID"           NUMBER(20, 0),
  "SHORT_TEXT"        VARCHAR2(100 BYTE),
  "FULL_TEXT"         VARCHAR2(2000 BYTE),
  "TITLE"             VARCHAR2(30 BYTE),
  "CREATION_DATE"     TIMESTAMP(6),
  "MODIFICATION_DATE" DATE
)

Comments表：

CREATE TABLE "COMMENTS"
(
  "COMMENT_ID"    NUMBER(20, 0),
  "COMMENT_TEXT"  VARCHAR2(100 BYTE),
  "CREATION_DATE" TIMESTAMP(6),
  "NEWS_ID"       NUMBER(20, 0)
)

是否有一种方法来选择所有的新闻按评论的数量排序？

Answer 1

您可以通过对评论数量的聚合查询来加入新闻表：

SELECT    news.*, NVL(num_comments, 0) AS num_comments
FROM      news
LEFT JOIN (SELECT   news_id, COUNT(*) AS num_comments
           FROM     comments
           GROUP BY news_id) cmt on cmt.news_id = news.news_id
ORDER BY  num_comments DESC