为什么只有输入顺序特殊时此MySQL查询才返回结果? [英] Why this MySQL query returns result only if input is in a particular order?

问题描述

我有一个表格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   ║ 
║ 9999    ║ 101  ║ 6   ║ 
║ 9999    ║ 102  ║ 8   ║
╚═════════╩══════╩═════╝

我有类似的查询

select distinct group_concat(lot order by lot)
from `mytable`
group by product
having group_concat(tag order by tag) = '101,102';

应该给我这样的输出；

Which is suppose to give me an 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.

代码工作正常.但有一个问题.如果我给输入101,102，查询工作正常.但是，如果我输入102,101作为输入，则不会获得任何行，但是我想获得与先前条件相同的准确结果，而忽略了给出标签号的顺序.另外，有时输入的数字可能会超过两个，例如101,102,103，101,102,103,104等.

The code works fine. But there is a problem. If i give input 101,102, the query works fine. But if I give 102,101 as input, then I get no rows, but I want to get the exact result as in the previous condition, ignoring the order in which the tag numbers are given. Also, sometimes there can be more than two numbers as input like 101,102,103, 101,102,103,104 etc.

我该怎么做?这是一个小提琴 http://sqlfiddle.com/#!9/7a78bb/11/0

How can i do this? Here is a fiddle http://sqlfiddle.com/#!9/7a78bb/11/0

推荐答案

虽然我倾向于使用php和mysql的组合，但我想我可以玩一下，看看是否可以提出一个纯SQL版本的这个:-

While I would tend to use a combination of php and mysql, I thought I would have a play and see if I could come up with a pure SQL version of this:-

SELECT DISTINCT GROUP_CONCAT(lot ORDER BY lot)
FROM `mytable`
GROUP BY product
HAVING COUNT(DISTINCT IF(FIND_IN_SET(tag, '101,102'), tag, NULL)) = 1 + LENGTH('101,102') - LENGTH(REPLACE('101,102', ',', ''))
AND COUNT(tag) = 1 + LENGTH('101,102') - LENGTH(REPLACE('101,102', ',', ''));

这并不关心以逗号分隔的值列表位于哪个顺序.但是效率不高！

This will not care which order the comma separated list of values is in. But is not going to be efficient!

这篇关于为什么只有输入顺序特殊时此MySQL查询才返回结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！