如何使用 TOP 1 信息和来自 Top 1 信息的排序依据选择不同的信息

Question

我有 2 个表，看起来像：

CustomerInfo(CustomterID, CustomerName)
CustomerReviews(ReviewID, CustomerID, Review, Score)

我想搜索评论中的字符串并返回 CustomerInfo.CustomerID 和 CustomerInfo.CustomerName。但是，我只想显示不同的 CustomerID 和 CustomerName 以及它们的 CustomerReviews.Reviews 和 CustomerReviews.Score 之一代码>.我还想通过 CustomerReviews.Score 进行订购。

我不知道如何做到这一点，因为客户可以留下多条评论，但我只想要一份评分最高的客户列表。

有什么想法吗？

Answer 1

这是每组中最大的问题，在 Stack Overflow 上已经出现了数十次。

这是一个与窗口函数配合使用的解决方案：

WITH CustomerCTE (
  SELECT i.*, r.*, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Score DESC) AS RN
  FROM CustomerInfo i
  INNER JOIN CustomerReviews r ON i.CustomerID = r.CustomerID 
  WHERE CONTAINS(r.Review, '"search"')
)
SELECT * FROM CustomerCTE WHERE RN = 1
ORDER BY Score;

这是一个更广泛地与不支持窗口函数的 RDBMS 品牌配合使用的解决方案：

SELECT i.*, r1.*
FROM CustomerInfo i
INNER JOIN CustomerReviews r1 ON i.CustomerID = r1.CustomerID 
  AND CONTAINS(r1.Review, '"search"')
LEFT OUTER JOIN CustomerReviews r2 ON i.CustomerID = r2.CustomerID 
  AND CONTAINS(r1.Review, '"search"')
  AND (r1.Score < r2.Score OR r1.Score = r2.Score AND r1.ReviewID < r2.ReviewID)
WHERE r2.CustomerID IS NULL
ORDER BY Score;

我正在展示 CONTAINS() 函数，因为您应该使用 SQL Server 中的全文搜索工具，而不是使用 LIKE 带有通配符。

Answer 2

我投票支持比尔·卡文的答案，但我想我应该放弃另一个选择。

它使用 相关子查询，对于大数据集经常会出现性能问题，所以要谨慎使用。我认为唯一的好处是查询更容易立即理解。

select *
from [CustomerReviews] r
where [ReviewID] =
(
    select top 1 [ReviewID]
    from [CustomerReviews] rInner
    where rInner.CustomerID = r.CustomerID
    order by Score desc
)
order by Score desc

我没有添加字符串搜索过滤器，但可以轻松添加。

Answer 3

我认为这应该可以做到

select ci.CustomterID, ci.CustomerName, cr.Review, cr.Score
from CustomerInfo ci inner join 
(select top 1*
from CustomerReviews
where Review like '%search%'
order by Score desc) cr on ci.CustomterID = cr.CustomterID 
order by cr.Score