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 tags (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 lots, 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屋！