问题描述
我对此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，为列设置限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！