SQL，为列设置限制? [英] SQL, set limit for a column?

问题描述

我对此SQL查询有疑问

I have a problem with this SQL-Query

SELECT * 
FROM page p 
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to IN
(
  'Art-Rock-Band',
  'Echo-Pop-Preisträger',
  'Englische_Band',
  'Genesis_(Band)',
  'Grammy-Preisträger',
  'Peter_Gabriel',
  'Phil_Collins',
  'Popband',
  'Progressive-Rock-Band',
  'Rock_and_Roll_Hall_of_Fame'
)

它有效，并且在p.page_id = c.cl_from

现在，我想为每个类别设置一个限制，因为查询花费的时间太长.

Now I want to set a limit for every single category because the query takes too long.

我只想为'Art-Rock-Band'提供5个结果，为'Echo-Pop-Preisträger'等提供5个结果...

I want just 5 results for 'Art-Rock-Band', just 5 results for 'Echo-Pop-Preisträger' etc...

推荐答案

此解决方案有些冗长(除非其他人有更好的主意)，但是您可以使用UNION ALL来显示一系列遵循此模式的较小查询:

The solution to this is somewhat lengthy (unless someone else has a better idea) but you can use UNION ALL to display the top 5 results from a series of smaller queries following this pattern:

SELECT * FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Art-Rock-Band'
LIMIT 5
UNION ALL
SELECT * FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Echo-Pop-Preisträger'
LIMIT 5
...

您也可以将5替换为变量，这样就可以通过简单的更改控制从所有内容中获得多少结果:

You could also get fancy and replace the 5 with a variable so that you can control how many results you get from everything with a simple change:

DECLARE @num INT DEFAULT 5;

SELECT * FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Art-Rock-Band'
LIMIT @num
UNION ALL
SELECT * FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Echo-Pop-Preisträger'
LIMIT @num
...

作为附加值，我在实例中添加了第二个声明(当然要注释一下)，该实例可能要按百分比而不是设置的数字来调用.

As an added value, I put in a second declaration (commented out, of course) of the same variable in the instance you might want to recall by percent rather than a set number.

我希望这至少可以帮助您指出正确的方向.

I hope this helps point you in the right direction at least.

-C§

对于SQL Server，在每个查询中，在UNION ALL之前将LIMIT @num替换为TOP @num，并将DEFAULT替换为=.您还可以在第二行中将@num声明为字符串并使用PERCENT关键字，但只能在SQL Server中使用，因为MySQL和Oracle均不支持它.

For SQL Server, replace LIMIT @num with TOP @num before the UNION ALL in each query and replace the DEFAULT with =. You can also have a second line to declare the @num as a string and use the PERCENT keyword, but only in SQL Server as neither MySQL nor Oracle supports it.

对于Oracle，您可以类似地用WHERE子句:AND ROWNUM <= @num替代它.您还希望更新DECLARE语句，以在冒号前面加上等号，以使=变为:=.

For Oracle, you can replace it similarly with an addition to the WHERE clause: AND ROWNUM <= @num. You also want to update the DECLARE statement to prepend a colon to the equals so = becomes :=.

这应该说明与上述MySQL示例的主要区别，其他任何后面的问题在其他两种格式中都存在类似的问题.可以在此处找到更多说明: http://www.w3schools.com/sql/sql_top.asp .

This should account for the primary differences from the above MySQL examples for any coming behind that have a similar question in the other two formats. More explanation can be found here: http://www.w3schools.com/sql/sql_top.asp.

这篇关于SQL，为列设置限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！