MySQL查询:对具有外部连接的表列使用限制

时间:2022-08-11 00:15:05

I've got one main users database table and a "meta" table to outer join. Basically the essential query is

我有一个主用户数据库表和一个“meta”表到外部连接。基本的查询是

SELECT * FROM users_table 
LEFT OUTER JOIN meta_table ON users_table.id = meta_table.user_id 
ORDER BY users_table.id ASC

But I have to limit results on the users_table ID column. If I use the standard LIMIT clause, the query will count also meta values.

但是我必须限制users_table ID列上的结果。如果我使用标准的LIMIT子句,查询也将计数元值。

Would be possible to limit on the user's table ID column and associate metas with the join?

是否可能限制用户的表ID列并将元数据与连接关联?

2 个解决方案

#1


3  

You can try to use subquery. For example

您可以尝试使用子查询。例如

SELECT * FROM meta_table 
         RIGHT OUTER JOIN (SELECT * FROM users_table 
                           WHERE status = 1
                           ORDER BY users_table.id ASC LIMIT 10) as tbl_user 
         ON meta_table.user_id = tbl_user.id

#2


1  

Thanks to @mergenchik to point me on the right direction.

感谢@mergenchik为我指明了正确的方向。

I ended up with a slightly different query to keep users table data at the beginning and be able to join a new table in future

我最后得到了一个稍微不同的查询,以在开始时保留users表数据,并能够在将来加入一个新的表

SELECT * FROM 
    (SELECT * FROM users_table ORDER BY id ASC LIMIT 15) as users_table
LEFT OUTER JOIN meta_table 
ON meta_table.user_id = users_table.id

#1


3  

You can try to use subquery. For example

您可以尝试使用子查询。例如

SELECT * FROM meta_table 
         RIGHT OUTER JOIN (SELECT * FROM users_table 
                           WHERE status = 1
                           ORDER BY users_table.id ASC LIMIT 10) as tbl_user 
         ON meta_table.user_id = tbl_user.id

#2


1  

Thanks to @mergenchik to point me on the right direction.

感谢@mergenchik为我指明了正确的方向。

I ended up with a slightly different query to keep users table data at the beginning and be able to join a new table in future

我最后得到了一个稍微不同的查询,以在开始时保留users表数据,并能够在将来加入一个新的表

SELECT * FROM 
    (SELECT * FROM users_table ORDER BY id ASC LIMIT 15) as users_table
LEFT OUTER JOIN meta_table 
ON meta_table.user_id = users_table.id