在SQL Server中选择不同的TOP 5

I can't get this sql query right...

我无法得到这个sql查询。

I want the top 5 latest comments from tblComment. The problem is that I get more then one comment with the same ProductID. I don't want that.

我想要tblComment的5个最新评论。问题是，我得到的评论不止一条。我不希望这样。

SELECT DISTINCT TOP 5
   tblProduct.ProductID, 
   tblProduct.ProductName, 
   tblComment.DateAdded
FROM   
    tblComment 
INNER JOIN 
    tblProduct ON tblProduct.ProductID = tblComment.ProductID
ORDER BY 
    tblComment.DateAdded DESC

What am I doing wrong?

我做错了什么?

3 个解决方案

#1

Assuming your comment table has an id field try this:

假设您的注释表有id字段，请尝试以下操作:

SELECT TOP 5

tblProduct.ProductID, 
tblProduct.ProductName, 
tblComment.DateAdded

FROM   tblComment 
JOIN   tblProduct ON tblProduct.ProductID = tblComment.ProductID
JOIN   (Select ProductID, max(id) as maxid From tblComment Group By ProductId) t on tblComment.id = t.maxid 

ORDER BY tblComment.DateAdded DESC

#2

You would have to sub select - use the following example to suit your needs.

您将不得不子选择—使用以下示例来满足您的需求。

SELECT TOP 5 tblProduct.ProductID, 
          tblProduct.ProductName, 
          tblComment.DateAdded
FROM   tblComment INNER JOIN
    tblProduct ON tblProduct.ProductID = tblComment.ProductID
    and tblProduct.ProductID 
      IN (
            SELECT tblProduct.ProductID 
            FROM tblComment 
                   INNER JOIN tblProduct ON tblProduct.ProductID = tblComment.ProductID
            GROUP BY tblProduct.ProductID 
            HAVING count( tblProduct.ProductID  ) =1
         )

#3

Products ranked by time of latest comment.

产品排名时间按最新评论。

This approach uses a CTE and and a rank function. This query is small but on larger queries these functions can make things more organized and readable.

这种方法使用CTE和rank函数。这个查询很小，但是在较大的查询中，这些函数可以使事情更有条理和可读性。

with lastComment as (
  select c.productID, max(DateAdded) DateAdded, 
    row_number() over(order by max(dateAdded)) rank
  from tblComment c
  group by c.productID

)

SELECT
   tblProduct.ProductID, 
   tblProduct.ProductName, 
   tblComment.DateAdded
FROM   
    tblProduct 
    join lastComment ON tblProduct.ProductID = lastCommnet.ProductID
WHERE
   lastComment.rank >= 5
ORDER BY lastComment.rank

#1