限制 SQL JOIN [英] LIMITing an SQL JOIN
问题描述
我正在尝试限制以下 SQL 语句.
I am trying to limit the following SQL statement.
SELECT expense.*, transaction.* FROM expense
INNER JOIN transaction ON expense_id = transaction_expense_id
我想做的是限制父"行的数量.IE.如果我执行 LIMIT 1,我将只收到一项费用项目,但仍会收到与之相关的所有交易.
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.
这将如何实现?
在这个阶段,如果我做 LIMIT 1,我得到一笔费用,只有一笔交易.
At this stage, if I do LIMIT 1, I get one expense, and only one transaction.
推荐答案
所以假设我们可以排除 user 表,它可以重写为:
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)
这会做你想要的吗?显然,您需要谨慎对待您的费用 ID 将返回的顺序,因此您可能希望使用 ORDER BY 任何内容.
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.
鉴于您在下面的评论中描述的 MySQL 限制,也许这会起作用:
Given the MySQL limitation described in your comment below, maybe this will work:
select * from (select id from expense order by WHATEVER limit 1) as t1, transaction where expense_id=transaction_expense_id;
本
这篇关于限制 SQL JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!