I'm running a simple query with a join, similar to
我正在运行一个带连接的简单查询，类似于
SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON ... LIMIT 5
As t1 has-many rows in t2 ( any number above 2 ) the LIMIT statement does not return the first 5 rows from t1 and corresponding entries from t2, but 5 rows which usually include 2-3 rows from t1.
由于t1在t2中有许多行（任何数字大于2），LIMIT语句不返回t1的前5行和t2的相应条目，而是5行，通常包括来自t1的2-3行。
How can I write this query to get the first 5 rows from t1 and the corresponding entries from t2?
如何编写此查询以从t1获取前5行以及从t2获取相应的条目？
Using MySQL 5.0.45.
使用MySQL 5.0.45。
4 个解决方案
#1
7
SELECT t3.a, t2.b FROM (SELECT * FROM t1 LIMIT 5) t3
LEFT JOIN t2 ON ...
Note that if you use limit without an 'order by' clause, it is not defined which 5 rows you will get. Consider adding an 'order by' clause if this is not what you want.
请注意，如果您使用不带'order by'子句的限制，则不会定义您将获得的5行。如果这不是您想要的，请考虑添加“order by”子句。
#2
2
This is a classic pagination query. I suggest breaking it down into two queries:
这是一个经典的分页查询。我建议将其分解为两个查询：
SELECT DISTINCT t1.id FROM t1 LEFT JOIN t2 ON ... LIMIT 5
Take these id's and place them in the following query:
获取这些ID并将它们放在以下查询中：
SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON ... WHERE t1.id IN (?,?,?,?,?)
#3
0
I believe the following will do the trick:
我相信以下内容可以解决问题：
SELECT t1.a, (SELECT t2.b FROM t2 WHERE t2... = t1...) AS b FROM t1 LIMIT 5
#4
0
You can group it by the unique column in t1:
您可以按t1中的唯一列对其进行分组：
SELECT * FROM t1 JOIN t2 ON ... GROUP BY t1.id LIMIT 5
But do you need the t2 table to be in a specific order?
但是你需要t2表按特定顺序排列吗？
#1
7
SELECT t3.a, t2.b FROM (SELECT * FROM t1 LIMIT 5) t3
LEFT JOIN t2 ON ...
Note that if you use limit without an 'order by' clause, it is not defined which 5 rows you will get. Consider adding an 'order by' clause if this is not what you want.
请注意，如果您使用不带'order by'子句的限制，则不会定义您将获得的5行。如果这不是您想要的，请考虑添加“order by”子句。
#2
2
This is a classic pagination query. I suggest breaking it down into two queries:
这是一个经典的分页查询。我建议将其分解为两个查询：
SELECT DISTINCT t1.id FROM t1 LEFT JOIN t2 ON ... LIMIT 5
Take these id's and place them in the following query:
获取这些ID并将它们放在以下查询中：
SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON ... WHERE t1.id IN (?,?,?,?,?)
#3
0
I believe the following will do the trick:
我相信以下内容可以解决问题：
SELECT t1.a, (SELECT t2.b FROM t2 WHERE t2... = t1...) AS b FROM t1 LIMIT 5
#4
0
You can group it by the unique column in t1:
您可以按t1中的唯一列对其进行分组：
SELECT * FROM t1 JOIN t2 ON ... GROUP BY t1.id LIMIT 5
But do you need the t2 table to be in a specific order?
但是你需要t2表按特定顺序排列吗？