选择有限数量的不同行

问题描述：

I have a table in this format:

Name     | Description    | option_id
"Apple"  | "Sweet fruit"  | 3
"Apple"  | "Sour fruit"   | 4
"Pear"   | "Sweet fruit"  | 5
"Orange" | "Orange fruit" | 3
"Carrot" | "Vegetable"    | 3

I run the query SELECT * FROM table WHERE Description LIKE '%fruit%' and get this:

Name     | Description    | option_id
"Apple"  | "Sweet fruit"  | 3
"Apple"  | "Sour fruit"   | 4
"Pear"   | "Sweet fruit"  | 5
"Orange" | "Orange fruit" | 3

I want to get only 2 distinct fruit Names, so the result of query should be:

Name     | Description    | option_id
"Apple"  | "Sweet fruit"  | 3
"Apple"  | "Sour fruit"   | 4
"Orange" | "Orange fruit" | 3

But I obviously don't get 3 records, as using ORDER BY Name ASC LIMIT 2 causes query to stop when there are 2 records ready.

I want to produce as many records as possible, as soon as their name is in first 10 of distinct. Here is what I came up so far:

SELECT * FROM table WHERE Name IN (
    SELECT DISTINCT Name ORDER BY Name ASC LIMIT 2
) ORDER by option_id ASC

Is there a better way of doing it without nesting? Query should be as fast as possible.

P.S. To complicate the matters, this is not my full query, but a mere part of it. In full, it looks something like this (without LIMIT):

SELECT table.*,
    MATCH (Name, Description) AGAINST ('fruit') AS relevance,
    MATCH (Name) AGAINST ('fruit') AS name_relevance
FROM table
WHERE MATCH (Name, Description) AGAINST ('fruit')
GROUP BY Name
ORDER BY name_relevance DESC, relevance DESC, Name ASC

And I Name is more than just a name, but also some information about it, such as "Fresh Green Apple", "Apple Fruit", etc.

我有一个这种格式的表： p>

  Name | 说明|  option_id 
“Apple”|  “甜蜜的水果”|  3 
“Apple”|  “酸果”|  4 
“梨”|  “甜蜜的水果”|  5 
“橙色”|  “橙子水果”|  3 
“胡萝卜”|  “蔬菜”|  3 
  code>  pre> 
 
 我运行查询 SELECT * FROM table WHERE描述LIKE'％fruit％' code>并得到这个： p> \  n 
 
 名称| 说明|  option_id 
“Apple”|  “甜蜜的水果”|  3 
“Apple”|  “酸果”|  4 
“梨”|  “甜蜜的水果”|  5 
“橙色”|  “橙子水果”|  3 
  code>  pre> 
 
 我想只获得2个不同的水果名称 em>，因此查询的结果应为： p> 
 \  n 
 名称| 说明|  option_id 
“Apple”|  “甜蜜的水果”|  3 
“Apple”|  “酸果”|  4 
“橙色”|  “橙子水果”|  3 
  code>  pre> 
 
 但我显然没有得到3条记录，因为使用 ORDER BY Name ASC LIMIT 2  code>会导致查询在有 准备好了2个记录。 p> 
 
 
我希望尽可能多地生成记录，只要他们的名字在前10个不同。
这是我到目前为止所得到的： p  > 
 
 

  SELECT * FROM table WHERE Name IN（
 SELECT DISTINCT Name ORDER BY Name ASC LIMIT 2 
）ORDER by option_id ASC 
  code>  pre> 
 \  n 有没有更好的方法可以不嵌套？ 查询应该尽可能快。 p> 
 
 
  P.S。 strong>为了使问题复杂化，这不是我的完整查询，而只是它的一部分。 完整，它看起来像这样（没有 LIMIT  code>）： p> 
 
 
  SELECT table。*，
 MATCH（Name，Description）AGAINST（'  fruit'）AS相关，
 MATCH（名称）AGAINST（'fruit'）AS name_relevance 
FROM table 
WHERHER MATCH（名称，描述）AGAINST（'fruit'）
GROUP BY Name 
ORDER BY name_relevance DESC，related DESC， 名称ASC 
  code>  pre> 
 
 我姓名不仅仅是一个名称，还有一些关于它的信息，例如“Fresh Green Apple”，“Apple Fruit”等 。 p> 
  div>

答

One way or another, you need to get the list of names that you are accepting. Doing this in a subquery, as you have, is probably the simplest.

You can also use a temporary table which will probably speed this up a bit.

Perhaps Try Comparing these two. I suspect the second will be faster with bigger tables.

Here is one that's essentially the same as yours I believe

WITH fruit AS (SELECT TOP 2 DISTINCT Name FROM table1 WHERE Description LIKE '%fruit%' ORDER BY Name ASC)

SELECT * FROM table WHERE Name IN (
    SELECT * FROM fruit
) ORDER by option_id ASC

This one uses the Temp table

Create TABLE #fruit (Name NVARCHAR(50))
INSERT INTO #fruit
SELECT TOP 2 DISTINCT Name FROM table1 WHERE Description LIKE '%fruit%' ORDER BY Name ASC

SELECT * FROM table WHERE Name IN (
    SELECT * FROM #fruit
) ORDER by option_id ASC

Edit for MySQL

Here is a TempTable solution for MySQL

CREATE TEMPORARY TABLE fruit
SELECT DISTINCT Name FROM table1 WHERE Description LIKE '%fruit%' ORDER BY Name ASC LIMIT 2

SELECT * FROM table WHERE Name IN (
    SELECT * FROM fruit
) ORDER by option_id ASC

Edit to use Join

This should preserve the performance gains above, although it might be worth testing.

CREATE TEMPORARY TABLE fruit
SELECT DISTINCT Name, order FROM table1 WHERE Description LIKE '%fruit%' ORDER BY Name ASC LIMIT 2

SELECT * FROM table t
JOIN fruit f ON t.Name = f.Name
ORDER by f.order

答

Here's an alternative that may prove faster:

SELECT a.* 
FROM table1 a
JOIN (SELECT DISTINCT Name 
      FROM table1
      WHERE Description LIKE '%fruit%'
      ORDER BY Name ASC LIMIT 2)b
 ON a.Name = b.Name

Demo: SQL Fiddle

选择有限数量的不同行

相关推荐