限制 SQL JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/494974/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LIMITing an SQL JOIN
提问by Thomas R
I am trying to limit the following SQL statement.
我试图限制以下 SQL 语句。
SELECT expense.*, transaction.* FROM expense
INNER JOIN transaction ON expense_id = transaction_expense_id
What I want to do, is limit the number of 'parent' rows. IE. if I do a LIMIT 1, I would receive only one expense item, but still get all transactions associated with it.
我想要做的是限制“父”行的数量。IE。如果我执行 LIMIT 1，我将只收到一项费用项目，但仍会收到与之相关的所有交易。
How would this be achieved?
这将如何实现？
At this stage, if I do LIMIT 1, I get one expense, and only one transaction.
在这个阶段，如果我做 LIMIT 1，我会得到一笔费用，并且只有一笔交易。
采纳答案by Ben
So assuming we can exclude the user table, it could be rewritten as:
所以假设我们可以排除用户表，它可以重写为：
select * from expense, transaction where expense_id = transaction_expense_id
Now if you want to apply a limit, you could do it like this:
现在如果你想应用一个限制，你可以这样做：
select * from expense, transaction where expense_id = transaction_expense_id and
expense_id in (select expense_id from expense limit 1)
Would that do what you wanted? Obviously you need to be cautious about what order your expense_ids are going to come back in, so you probably want to use ORDER BY whatever.
那会做你想要的吗？显然，您需要谨慎对待您的费用 ID 将返回的顺序，因此您可能想要使用 ORDER BY 任何内容。
Edit: Given the MySQL limitation described in your comment below, maybe this will work:
编辑：鉴于您在下面的评论中描述的 MySQL 限制，也许这会起作用：
select * from (select id from expense order by WHATEVER limit 1) as t1, transaction where expense_id=transaction_expense_id;
Ben
本
回答by David Schmitt
You'll have to specify which expenseitem you want to get. The most expensive? The newest? Then join against a subquery that returns only that:
您必须指定要获得的费用项目。最昂贵的？最新的？然后加入一个只返回以下内容的子查询：
SELECT
expense.*, transaction.*, user.*
FROM
(SELECT * FROM expense WHERE ...) AS expense
INNER JOIN
transaction ON expense_id = transaction_expense_id
回答by Thomas R
Since upgrading the SQL server is not an option, I may end up doing two queries.
由于升级 SQL 服务器不是一种选择，我最终可能会进行两次查询。
expenses = SELECT * FROM expense ... LIMIT x
foreach expenses as expense
expense.transactions = SELECT * FROM transacion WHERE transaction_expense_id = expense.expense_id
回答by jschicago
Even though this is old... came across this in a search and thought I'd add a couple thoughts, given the inability to use limit in a subquery...
即使这是旧的......在搜索中遇到了这个并认为我会添加一些想法，因为无法在子查询中使用限制......
select e.expense_id, transaction.*
from (select max(expense_id) from expense) e
inner join transaction t ON e.expense_id = t.transaction_expense_id
or if you want to have all columns from expenseand not just expense_idyou could nest subqueries.
或者，如果您想拥有所有列，expense而不仅仅是expense_id您可以嵌套子查询。
select e.*, t.*
from (
select e1.*
from expense e1 inner join (select max(expense_id) from expense) e2
on e1.expense_id = e2.expense_id
) e inner join transaction t on e.expense_id = t.transaction_expense_id
