时间戳

首页 > 解决方案 > MySQL 对第二次加入查询的限制

mysql - MySQL 对第二次加入查询的限制

问题描述

我不确定如何限制查询中的第二次加入。在我准备的小提琴中，我需要限制结果中每个用户的行数。

表一：

id | word
----------
1  | xyz
2  | zzz

表b：

name  | word
------------
Peter | xyz
John  | xyz
Jane  | xyz

表c：

name  | product
------------
Peter | blah
Peter | blah2
Peter | blah3
Peter | blah4
Peter | blah5
Peter | blah6
John | hello
John | world
John | blah

期望的结果：表 b 的前两个条目（用户 Peter 和 John）和每个用户在表 c 上加入的前两个条目。

name  | word | product
----------------------
Peter | xyz | blah
Peter | xyz | blah2
John  | xyz | hello
John  | xyz | world

我目前的查询是

select c.name, first_join.word, c.product from (
select b.* from a
left join b on b.word=a.word
where a.id=1
limit 2
) first_join
left join c on c.name=first_join.name

它产生结果：

name  | word | product
------------------------------
Peter | xyz  | blah
Peter | xyz  | blah2
Peter | xyz  | blah3
Peter | xyz  | blah4
Peter | xyz  | blah5
Peter | xyz  | blah6
John  | xyz  | hello
John  | xyz  | world
John  | xyz  | blah

你有什么想法？我找到了另一个讨论这个问题的线程，但我无法将其映射到我的案例。我知道结构并不完美，但我不允许更改表 c 的设计。

标签： mysqljoinlimit

解决方案

让我们剖析您的查询：

SELECT b.* FROM a
LEFT JOIN b ON b.word=a.word
WHERE a.id=1
LIMIT 2
-- This sub-query returns the following result:
+-------+------+
| name  | word |
+-------+------+
| Peter | xyz  |
| John  | xyz  |
+-------+------+

如果没有来自子查询的完整语法，您的主要语法将如下所示：

SELECT c.name, first_join.word, c.product FROM first_join
LEFT JOIN c ON c.name=first_join.name;

让我们将 替换c.name, first_join.word, c.product为*。然后你会得到这样的结果：

+-------+------+-------+---------+
| name  | word | name  | product |
+-------+------+-------+---------+
| Peter | xyz  | Peter | blah    |
| Peter | xyz  | Peter | blah2   |
| Peter | xyz  | Peter | blah3   |
| Peter | xyz  | Peter | blah4   |
| Peter | xyz  | Peter | blah5   |
| Peter | xyz  | Peter | blah6   |
| John  | xyz  | John  | hello   |
| John  | xyz  | John  | world   |
| John  | xyz  | John  | blah    |
+-------+------+-------+---------+

现在，在这里你问自己这是否是你想要的？如果不是，您需要确保您了解您在此处尝试执行的操作的逻辑。例如，为什么您的first_join子查询返回两行，但是当您LEFT JOIN返回所有行时table c？嗯，答案是因为你的ON c.name=first_join.name. table c无论您在子查询中施加的限制如何，它都会匹配所有出现的 peter 和 john 。如果您对外部查询设置限制，它将无法正常工作。

tl;dr 如果您使用的是 MySQL 8.0，您可以尝试以下查询：

SELECT name,word,product FROM
(SELECT 
    b.name, b.word,c.product,
    ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY c.name DESC) AS wRow
FROM 
a JOIN
b ON a.word=b.word JOIN
c ON c.name=b.name
WHERE a.id=1) r
WHERE wRow IN (1,2)
ORDER BY name DESC;

小提琴

另外两个例子：

-- First: by using INNER JOIN and UNION (as suggested in the comment).
SELECT b.name,b.word,r.product FROM a JOIN b ON a.word=b.word JOIN
((SELECT * FROM c WHERE c.name='peter' LIMIT 2) UNION
(SELECT * FROM c WHERE c.name='john' LIMIT 2)) r
ON b.name=r.name 
WHERE a.id=1;

-- Second: by using LEFT JOIN.    
SELECT b.Name,b.Word,IF(p.product IS NULL,j.product,p.product) AS 'Product' 
FROM a JOIN b ON a.word=b.word LEFT JOIN
(SELECT * FROM c WHERE NAME='peter' LIMIT 2) p ON b.name=p.name LEFT JOIN
(SELECT * FROM c WHERE NAME='john' LIMIT 2) j ON b.name=j.name
WHERE a.id=1 AND b.name IN (p.name,j.name);

更新：好的。不漂亮，但这个查询可以工作。我已经在 MySQL 5.7 上进行了测试。;)

SELECT b.Name,b.Word,c.Product FROM a 
JOIN b ON a.word=b.word 
JOIN c ON b.name=c.name 
JOIN (SELECT c.name,
REPLACE(SUBSTRING_INDEX(GROUP_CONCAT(product SEPARATOR ' '),' ',2),' ',',') prod 
FROM c GROUP BY NAME) r
ON b.name=r.name WHERE FIND_IN_SET(c.product,prod);

但是，如果您至少有一列具有自动增量值，那将会容易得多。

推荐阅读