如何在存储过程中使用SQL查询停止重复数据

Question

我是在SQL Server中创建一个存储过程，用于显示feed和feed评论，但我正在使用重复数据创建此存储过程，此处也显示了下面的我已经在我的表中列出了我的表格。请帮助我如何使用SQL Server显示我的满意度数据。

feed 表：

    FeedID | userId  | Caption | TotalComments | InsertDateTime
    1         101          Test        3              2017-05-04 18:51:23.960
    2         102          Party       2              2017-05-03 17:51:23.960
    3         103          event       3              2017-05-02 18:10:23.960

feedComment 表：

    FeedCommentID | FeedID | UserId | Comment | InsertDateTime
    1                  1      102      Good          2017-05-04 18:51:23.960
    2                  1      103      Bad           2017-05-04 16:51:23.960
    3                  2      102      Very Good     2017-05-04 18:51:23.960
    4                  1      101      ....          2017-05-04 14:51:23.960

预期输出：

    FeedID | userId  | Caption | TotalComments | InsertDateTime
    1         101          Test        3              2017-05-04 18:51:23.960
    2         102          Party       2              2017-05-03 17:51:23.960
    3         103          event       3              2017-05-02 18:10:23.960

在我的查询中，我得到了此输出：

    FeedID | userId  | Caption | TotalComments | InsertDateTime
    1         101          Test        3              2017-05-04 18:51:23.960
    1         101          Test        3              2017-05-04 18:51:23.960  
    1         101          Test        3              2017-05-04 18:51:23.960
    2         102          Party       2              2017-05-03 17:51:23.960
    3         103          event       3              2017-05-02 18:10:23.960

这是我的表格和数据，在下面我写了此查询，但它没有返回适当的数据：

SELECT 
    C.FeedID, *,
    (SELECT COUNT(*) 
     FROM FeedComment 
     WHERE FeedID = C.FeedID ) AS TotalComments
FROM 
    Feed C
INNER JOIN 
    Users U ON C.UserId = U.UserId
-- here I use a left join that's not returning proper data; 
-- without this left join proer data is returned, but I want to 
-- write this join so any one know how can manage 
LEFT JOIN
    FeedComment CC ON CC.FeedID = C.FeedID  
ORDER BY 
    CC.InsertDateTime DESC  
    OFFSET (1- 1) * 20 ROWS
    FETCH NEXT 20 ROWS ONLY

Answer 1

尝试此查询，

SELECT C.FeedID,userId,MAX(CC.CaptionInsertDateTime) AS CaptionInsertDateTime,                 
           COUNT(*) AS TotalComments
    FROM Feed C
    INNER JOIN Users U ON C.UserId = U.UserId
    LEFT join FeedComment CC ON CC.FeedID = C.FeedID 
    GROUP BY C.FeedID,userId
    ORDER BY MAX(CC.InsertDateTime) DESC  OFFSET (1- 1) * 20 ROWS
    FETCH NEXT 20 ROWS ONLY

或

SELECT C.FeedID,*                 
       ,(SELECT COUNT(*) FROM FeedComment WHERE FeedID = C.FeedID ) AS TotalComments
       ,(SELECT MAX(InsertDateTime) FROM FeedComment CC WHERE CC.FeedID = C.FeedID) AS InsertDateTime
FROM Feed C
INNER JOIN Users U ON C.UserId = U.UserId
ORDER BY InsertDateTime DESC  OFFSET (1- 1) * 20 ROWS
FETCH NEXT 20 ROWS ONLY