在 SQL Server 中选择 DISTINCT TOP 5
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10093912/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT DISTINCT TOP 5 in SQL Server
提问by user1007103
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. 问题是我收到的评论不止一条ProductID。我不想要那个。
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?
我究竟做错了什么？
回答by brendan
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
回答by Mez
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
)
回答by jason saldo
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
