如何根据列值组合检索唯一行? [英] How to retrieve unique rows based on column value combinations?

问题描述

我有一个表格mytable如下；

╔═════════╦══════╦═════╗
║ 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   ║ 
╚═════════╩══════╩═════╝

我有输入101，102.我想要类似的输出

I have the input 101,102. I want the output like;

2,5
3,5

表示在表中将查找组合101,102，并返回具有不同批号的完全相同的组合.与此同时，我想避免重复的行.在这里，1111和5555具有相同的标签，并且具有与tag相同的相应批号，因此我只需要一行而不是2行.即使8888的101和102标记具有不同的lot，也不能考虑将其列出，因为它还包含标记103.这意味着，我想要具有完全101, 102组合的产品.简而言之，我不想要带有任何额外标签的产品，也不希望任何带有缺少标签的产品.

which means, in the table, it 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, 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. Which means, I want products with exact 101, 102 combination. In short, I dont want products with any extra tags, and i dont want anything with missing tags.

我该如何实现?

推荐答案

答案已针对最新问题进行了修改

注意:查询未经测试

SELECT GROUP_CONCAT(lot ORDER BY lot ASC SEPARATOR ',') 
from mytable 
having count(*) = 2 
  and GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ' ') = '101 102' 
group by product

旧答案

您可以使用group by来实现.

you can use group by to achieve this.

select tag, product from mytable where tag in (101,102) group by tag, product 

使用distinct也可能做到这一点，但是您已经对其进行了研究.我不记得在多列中是否可能有区别.我认为这也行得通...

this may also be possible using distinct, but you have take a look into it. i cant remember if distinct is possible in multiple columns. I think this will work too...

select distinct tag, product from mytable where tag in (101,102)

这篇关于如何根据列值组合检索唯一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！