英文:
I want to limit my SQL query based on a column
问题
我有两张表，一张叫做"user"，另一张叫做"payment"。一个用户可以拥有多笔支付记录。
例子：
用户1有2笔支付记录
用户2有5笔支付记录
用户3有10笔支付记录
用户4有7笔支付记录
我有以下查询：
select * from user inner join payment on payment.user_id = user.id limit 2
这个查询只会返回用户1和他的2笔支付记录。
但我想要返回用户1和用户2，每个用户都带有他们的支付记录。
英文:
I have two tables, one named user and the other named payment. A user can have multiple payments.
example:
user 1 with 2 payments
user 2 with 5 payments
user 3 with 10 payments
user 4 with 7 payments
I have this query:
select * from user inner join payment on payment.user_id = user.id limit 2
The query will return only user 1 with his 2 payments.
But I want to return user 1 and user 2, each with their payments.
答案1
得分: 1
如果我理解正确，您想返回两个用户的付款记录，如果是这样，请尝试以下代码：
select p.*
from payment p
inner join (
select id
from user
order by id
limit 2
) as u on u.id = p.user_id
英文:
If I understand well, you want to return the payments of two users, if so, try this:
select p.*
from payment p
inner join (
select id
from user
order by id
limit 2
) as u on u.id = p.user_id
答案2
得分: 0
如果您正在运行MySQL 8.0.14或更高版本，那么lateral join是一个不错的选择：
select u.*, p.*
from users u
cross join lateral (
select p.*
from payments p
where p.user_id = u.id
order by p.payment_date desc
limit 2
) p
where u.id in (1, 2)
order by u.id, p.payment_date
注意：
-
您确实希望在
limit与order by一起使用（否则数据库可能不会返回一致的结果）；我假设了payments表中有一个payment_date列，以此来获取每个用户的最新两次付款记录（值得一提的是，您最初的代码也存在相同的问题，不能保证多次运行相同数据集时始终返回相同的结果）。 -
为了提高性能，考虑在
payment(user_id, payment_date desc)上创建索引。 -
在连接多个表时，最好列出您在结果集中想要的列，而不要使用
*，这有助于明确意图，并避免当列在不同表中具有相同名称时出现冲突。 -
user是MySQL中的一个关键字，因此不适合作为列名（例如，users是可以的）。
在MySQL 8.0的早期版本中，还有一种替代方法是使用row_number()（如果每个用户有很多付款记录，可能效率会略低）：
select u.*, p.*
from users u
inner join (
select p.*,
row_number() over(partition by user_id order by payment_date desc) rn
from payments p
) p on p.user_id = u.id
where u.id in (1, 2) and p.rn <= 2
order by u.id, p.payment_date
英文:
If you are running MySQL 8.0.14 or higher, that's a good spot for a lateral join:
select u.*, p.*
from users u
cross join lateral (
select p.*
from payments p
where p.user_id = u.id
order by p.payment_date desc
limit 2
) p
where u.id in (1, 2)
order by u.id, p.payment_date
Notes:
-
you do want to use
order bywithlimit(otherwise the database may not return consistent results); I assumed anpayment_datecolumn in thepaymentstable for this purpose, so this gives you the two latest payments per user (for the record, your initial code has the same problem, and there is no guarantee that it will consistently return the same result when ran multiple times against the same dataset) -
for performance, consider an index on
payment(user_id, payment_date desc) -
when joining multiple tables, it is good practice to enumerate the columns you want in the resultset, rather than using
*- this clarifies the intent, and avoids conflicts when columns have the same name in different tables -
useris a keyword in MySQL, hence a bad choice for a column name (usersis not, for example)
In earlier versions of MySQL 8.0, an alternative is row_number() (which might scale less efficiently if there are many payments per user):
select u.*, p.*
from users u
inner join (
select p.*,
row_number() over(partition by user_id order by payment_date desc) rn
from payments p
) p on p.user_id = u.id
where u.id in (1, 2) and p.rn <= 2
order by u.id, p.payment_date
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论