如何在MySQL中限制JOIN查询返回的结果

Question

我正在尝试限制以下的SQL语句。

SELECT expense.*, transaction.* FROM expense
INNER JOIN transaction ON expense_id = transaction_expense_id

我想要做的是限制“父”行的数量。例如，如果我使用LIMIT 1，则只会收到一个费用项目，但仍会获得与其关联的所有交易。

如何实现这一点？我正在使用MySQL 5.0。

目前，如果我使用LIMIT 1，我只会得到一个费用和一个交易。

- rixth

---

我可能有点傻，但是你不需要在连接中包含用户表吗？ - Ben

正如 @rixth 指出的那样，您需要对 SELECT 中涉及的所有表格进行联接限定。 - Mitch Wheat

哎呀，忘记了用户表在里面！是我打错字了，问题已经解决了。 - Thomas R

MySQL 5.0 - Thomas R

Answer 1

假设我们可以排除用户表，那么它可以重写为：

select * from expense, transaction where expense_id = transaction_expense_id

现在如果你想应用一个限制，你可以这样做：

select * from expense, transaction where expense_id = transaction_expense_id and 
  expense_id in (select expense_id from expense limit 1)

这样做是否符合您的要求？显然，您需要小心考虑expense_ids返回的顺序，因此您可能需要使用ORDER BY来指定排序方式。

编辑：根据您在下面评论中描述的MySQL限制，也许这个方法可以解决问题：

select * from (select id from expense order by WHATEVER limit 1) as t1, transaction where expense_id=transaction_expense_id;

本

- Ben

---

"#1235 - 这个版本的MySQL还不支持'LIMIT & IN / ALL / ANY / SOME子查询'" 服务器版本：5.0.27-community-nt - Thomas R

子查询是做这件事的正确方式。你已经撞到了MySQL的限制；要么升级（到可能会修复此问题的5.1版本，或者转到Postgres）。否则你将不得不考虑非纯SQL解决方案或一些非常丑陋的东西。 - kquinn

将其标记为已接受的答案，因为它可以在另一个SQL环境中使用。 - Thomas R

@rixth：谢谢。我还添加了另一个可能适合你的解决方案... - Ben

:-) 这是个好消息！顺便说一句：这可能完全没有帮助，但我想知道你是否考虑过重命名表格中的列？如果你将 expense_id 重命名为 id，那么你可以用 expense.id 或者只用 id（当它不含糊时）来引用它。这可能会让事情变得更容易... - Ben

Answer 2

你需要指定你想要获取哪个支出项目。最昂贵的？最新的？然后使用一个子查询连接，只返回那个项目：

SELECT
    expense.*, transaction.*, user.*
FROM
    (SELECT * FROM expense WHERE ...) AS expense
INNER JOIN
    transaction ON expense_id = transaction_expense_id

- David Schmitt

---

如果我想要返回10个费用项目（以及相关的交易），该怎么说？ - Thomas R

@rixth：这将使用子查询返回的尽可能多的费用项目。 - David Schmitt

@Tom 把它改成 WHERE ... LIMIT 10) AS expense。我想是这样。 - Timmmm

Answer 3

由于升级SQL服务器不是一个选项，我可能最终会执行两个查询。

expenses = SELECT * FROM expense ... LIMIT x
foreach expenses as expense
    expense.transactions = SELECT * FROM transacion WHERE transaction_expense_id = expense.expense_id

- rixth

Answer 4

尽管这是旧的......在搜索中遇到了这个，我想加入一些想法，考虑到无法在子查询中使用limit...

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

或者如果您想从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

- jschicago