sql-server - 从组中选择最大限制 1

Question

我正在制作一个网页缓存系统。我想制作一个简单的页面排名系统和输出。问题是，我想显示每个唯一域相关性得分最高的记录集。一个域可能有多个记录，但具有不同的标题、描述等。问题是，它不是获取包含唯一域的 1 个记录集，而是对该唯一域的所有记录集进行分组并全部输出。我只想要每个组的每个唯一域具有最高相关性得分的记录集，然后再输出下一个(以及与该组具有最高相关性的不同域)

SELECT title, html, sum(relevance) FROM
  (
  SELECT title, html, 10 AS relevance FROM page WHERE title like ‘%about%’ UNION
  SELECT title, html, 7 AS relevance FROM page WHERE html like ‘%about%’ UNION
  SELECT title, html, 5 AS relevance FROM page WHERE keywords like ‘%about%’ UNION
  SELECT title, html, 2 AS relevance FROM page WHERE description like ‘%about%’
  ) results
GROUP BY title, html
ORDER BY relevance desc;

我得到:

domain1 title html
domain1 title html
domain1 title html
domain2 title html
domain2 title html
domain2 title html

我想要的是

domain1 title html
domain2 title html
domain3 title html
domain4 title html
domain5 title html

Answer 1

我不确定为什么你的代码可以工作，因为我认为你应该有

ORDER BY Sum(relevance) DESC

而不是

ORDER BY relevance DESC

也许这就是问题所在？

除此之外，还有这个呢。它很丑陋，但它会起作用。如果 SQL Server 了解如何稍后在查询中引用别名，那就更好了。但可惜。

SELECT title, html,Case When title LIKE '%about%' Then 10 Else 0 End +Case When html LIKE '%about%' Then 7 Else 0 End +Case When keywords LIKE '%about%' Then 5 Else 0 End +Case When description LIKE '%about%' Then 2 Else 0 End AS relevanceFROM pageWHERE Case When title LIKE '%about%' Then 10 Else 0 End +Case When html LIKE '%about%' Then 7 Else 0 End +Case When keywords LIKE '%about%' Then 5 Else 0 End +Case When description LIKE '%about%' Then 2 Else 0 End > 0ORDER BY Case When title LIKE '%about%' Then 10 Else 0 End +Case When html LIKE '%about%' Then 7 Else 0 End +Case When keywords LIKE '%about%' Then 5 Else 0 End +Case When description LIKE '%about%' Then 2 Else 0 End DESC;

或者也许只是轻微的重新排列:

SELECT title, html, relevanceFROM (SELECT title, html,Case When title LIKE '%about%' Then 10 Else 0 End +Case When html LIKE '%about%' Then 7 Else 0 End +Case When keywords LIKE '%about%' Then 5 Else 0 End +Case When description LIKE '%about%' Then 2 Else 0 End AS relevanceFROM page)WHERE relevance > 0ORDER BY relevance DESC;