sql查询获得一个tweet列表，每个tweet的最新10条评论都是由一个作家写的，比如“barack obama”

以下是表格结构，

create table WRITER (
id int primary key,
name varchar2(100)
)
create table TWEET(
id int primary key,
tweet varchar2(100),
createdDate TIMESTAMP,
writerid int foreign key references id of table WRITER
)
create table COMMENT (
id int primary key,
comment varchar2(1000),
createdDate TIMESTAMP,
tweetid int foreign key references id of table TWEET,
userid int foreign key references id of table USER
)
create table USER (
id int primary key,
name varchar2(100)
)

我尝试使用下面的查询，但它没有给出所需的输出。

SELECT id, tweetid, comment, createdDate
FROM COMMENT
WHERE tweetid in (SELECT id from TWEET WHERE writerid IN
                        (select id from WRITER WHERE name = 'Barack Obama'))
AND ROWNUM<11
GROUP BY id, tweetid, comment, createdDate
ORDER BY tweetid;

如何获得正确的输出？我需要最新的10条评论，每推特的'巴拉克奥巴马'

你可以使用分析函数 ROW_NUMBER 具体如下：

SELECT *
  FROM (
    SELECT C.ID,
           C.TWEETID,
           C.COMMENT,
           C.CREATEDDATE,
           ROW_NUMBER() OVER(
               PARTITION BY C.TWEETID
                ORDER BY C.CREATEDDATE DESC
           ) AS RN
      FROM COMMENT   C
      JOIN TWEET     T
    ON C.TWEETID = T.ID
      JOIN WRITER    W
    ON T.WRITEID = W.ID
     WHERE W.NAME = 'Barack Obama'
)
 WHERE RN <= 10;

样本数据：

SQL> SELECT * FROM WRITER;
    ID NAME
------ ----------------------------------------------------------------------------------------------------
     1 Barack Obama
SQL> SELECT * FROM TWEET;
    ID TWEET CREATEDDATE                                                                  WRITER_ID
------ ----- --------------------------------------------------------------------------- ----------
   100 T1    30-JUN-20 02.17.32.000000 PM                                                         1
   101 T2    16-JUN-20 02.21.51.000000 PM                                                         1
SQL> SELECT * FROM COMMENTS;
    ID COMMENTS   CREATEDDATE                                                                 TWEET_ID
------ ---------- --------------------------------------------------------------------------- --------
  1000 T1C1       30-JUN-20 02.17.58.000000 PM                                                     100
  1001 T1C2       30-JUN-20 03.17.58.000000 PM                                                     100
  2000 T2C1       30-JUN-20 02.21.51.000000 PM                                                     101
  2001 T2C2       29-JUN-20 02.21.51.000000 PM                                                     101
  2002 T2C2       28-JUN-20 02.21.51.000000 PM                                                     101
  2003 T2C2       27-JUN-20 02.21.51.000000 PM                                                     101
  2004 T2C2       26-JUN-20 02.21.51.000000 PM                                                     101
  2005 T2C2       25-JUN-20 02.21.51.000000 PM                                                     101
  2006 T2C2       24-JUN-20 02.21.51.000000 PM                                                     101
  2007 T2C2       23-JUN-20 02.21.51.000000 PM                                                     101
  2008 T2C2       22-JUN-20 02.21.51.000000 PM                                                     101
  2009 T2C2       21-JUN-20 02.21.51.000000 PM                                                     101
  2010 T2C2       20-JUN-20 02.21.51.000000 PM                                                     101
  2011 T2C2       19-JUN-20 02.21.51.000000 PM                                                     101
  2012 T2C2       18-JUN-20 02.21.51.000000 PM                                                     101
15 rows selected.
SQL>

查询输出：

SQL> SELECT *
  2    FROM (
  3      SELECT C.ID,
  4             C.TWEET_ID,
  5             C.COMMENTS,
  6             C.CREATEDDATE,
  7             ROW_NUMBER() OVER(
  8                 PARTITION BY C.TWEET_ID
  9                  ORDER BY C.CREATEDDATE DESC
 10             ) AS RN
 11        FROM COMMENTS   C
 12        JOIN TWEET     T
 13      ON C.TWEET_ID = T.ID
 14        JOIN WRITER    W
 15      ON T.WRITER_ID = W.ID
 16       WHERE W.NAME = 'Barack Obama'
 17  )
 18   WHERE RN <= 10;
    ID TWEET_ID COMMENTS   CREATEDDATE                                                                         RN
------ -------- ---------- --------------------------------------------------------------------------- ----------
  1001      100 T1C2       30-JUN-20 03.17.58.000000 PM                                                         1
  1000      100 T1C1       30-JUN-20 02.17.58.000000 PM                                                         2
  2000      101 T2C1       30-JUN-20 02.21.51.000000 PM                                                         1
  2001      101 T2C2       29-JUN-20 02.21.51.000000 PM                                                         2
  2002      101 T2C2       28-JUN-20 02.21.51.000000 PM                                                         3
  2003      101 T2C2       27-JUN-20 02.21.51.000000 PM                                                         4
  2004      101 T2C2       26-JUN-20 02.21.51.000000 PM                                                         5
  2005      101 T2C2       25-JUN-20 02.21.51.000000 PM                                                         6
  2006      101 T2C2       24-JUN-20 02.21.51.000000 PM                                                         7
  2007      101 T2C2       23-JUN-20 02.21.51.000000 PM                                                         8
  2008      101 T2C2       22-JUN-20 02.21.51.000000 PM                                                         9
  2009      101 T2C2       21-JUN-20 02.21.51.000000 PM                                                        10
12 rows selected.
SQL>

展开查看全部

sql查询获得一个tweet列表，每个tweet的最新10条评论都是由一个作家写的，比如“barack obama”

1条答案

相关问题

热门标签

最新问答