MySQL 查询根据特定标签组合在获取行时返回不需要的行 [英] MySQL query returns unwanted rows on fetching rows based on specific tag combinations
问题描述
我在 Windows 8 PC 上运行 PHP+MySQL.我有一个表 mytable
如下所示;
I run PHP+MySQL in my Windows 8 PC. I have a table mytable
like below;
╔═════════╦══════╦═════╗
║ product ║ tag ║ lot ║
╠═════════╬══════╬═════╣
║ 1111 ║ 101 ║ 2 ║
║ 1111 ║ 102 ║ 5 ║
║ 2222 ║ 103 ║ 6 ║
║ 3333 ║ 104 ║ 2 ║
║ 4444 ║ 101 ║ 2 ║
║ 5555 ║ 101 ║ 2 ║
║ 5555 ║ 102 ║ 5 ║
║ 6666 ║ 102 ║ 2 ║
║ 6666 ║ 103 ║ 5 ║
║ 7777 ║ 101 ║ 2 ║
║ 7777 ║ 102 ║ 5 ║
║ 7777 ║ 103 ║ 6 ║
║ 8888 ║ 101 ║ 1 ║
║ 8888 ║ 102 ║ 3 ║
║ 8888 ║ 103 ║ 5 ║
║ 9999 ║ 101 ║ 6 ║
║ 9999 ║ 102 ║ 8 ║
╚═════════╩══════╩═════╝
我有输入101
,102
.我想要这样的输出;
I have the input 101
,102
. I want the output like;
2,5
6,8
查询将查找组合101,102
,并返回完全相同的组合和不同批号.除此之外,我想避免重复的行.这里 1111
和 5555
具有相同的标签,与 tag
的对应批号相同(相同批号的完全相同的组合),所以我只想要一个行而不是 2 行.即使 8888
的标签 101
和 102
具有不同的 lot
,它也不能考虑上市,因为它还包括标签103
.简而言之,我想要具有精确 101, 102
组合的产品,我不想要带有任何额外标签的产品,我不想要任何缺少标签的产品.
The query will look for combinations 101,102
, and returns the exact same combinations with different lot number. Along with this, I want to avoid duplicate rows. Here 1111
and 5555
has same tags with same corresponding lot numbers to tag
s (exact same combinations with same lots), so I want only one row instead of 2 rows. Even though, 8888
has tags 101
and 102
with different lot
s, it cannot be considered for listing , since it includes tag 103
in addition. In short, I want products with exact 101, 102
combination, and I dont want products with any extra tags, and i dont want anything with missing tags.
我有一个类似下面的查询;
I have a query like below;
SELECT DISTINCT GROUP_CONCAT(m.lot) FROM mytable m
WHERE m.tag IN (101,102)
AND (SELECT COUNT(m.product) FROM mytable m2 WHERE m.product = m2.product)>1
GROUP BY m.product;
返回;
2,5
2
5,2
1,3
6,8
我该如何解决这个问题?有没有更好的方法来完成我正在寻找的东西?
How can I fix this? Is there any better method to accomplish what I am looking for?
这是从http://sqlfiddle.com/#!9/d11d6一个>
推荐答案
我建议为此使用 simple join:
I suggest using simple join for this:
SELECT DISTINCT a.lot, b.lot
FROM mytable a
INNER JOIN mytable b ON b.product = a.product AND NOT EXISTS (SELECT * FROM product WHERE tag NOT IN (a.tag, b.tag))
WHERE a.tag = 101 and b.tag = 102
这篇关于MySQL 查询根据特定标签组合在获取行时返回不需要的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!