问题描述
我的数据如下：
|User ID |Install Version |
|--------------------------------|
| Value A | Pattern 1 |
| Value B | Pattern 1 |
| Value A | Pattern 2 |
| Value C | Pattern 2 |
| Value D | Pattern 1 |
我只想选择1个或多个条目与匹配的用户ID 1 和模式2 。因此，在上面的示例中，我只想选择 Value a 。
I want to select only the user IDs where 1 or more entries match Pattern 1 and Pattern 2. So in the above example, I would only want to select Value a.
基本上，我想比较哪个用户ID已安装了软件产品的精简版和完整版。例如，由于值B 没有与模式1 和模式2 ，我不想选择它。
Basically, I want to compare which User Id's have installed both "Lite" and "Full" of a software product. For example, since Value B does not have entries paired with Pattern 1 AND Pattern 2, I don't want to select it.
用户ID条目很多，但安装的版本很少。我已经解决了这个问题了很长时间，但是仍然遇到了麻烦。
There will be many user ID entries but few installed Versions. I have played around with this problem for quite a while, but still I am having trouble with it.
推荐答案
我可以想到两种方式，一种是使用GROUP_CONCAT，另一种是使用子查询。
I can think of two ways, one with GROUP_CONCAT and the other using subquery.
SELECT user_id, GROUP_CONCAT(installed_version) as all_patterns
FROM your_table
GROUP BY user_id
HAVING all_patterns REGEXP 'Pattern 1'
AND all_patterns REGEXP 'Pattern 2';
-
SELECT * FROM
(
SELECT user_id, installed_version FROM your_table
WHERE installed_version LIKE 'Pattern 1'
) as tmp
WHERE installed_version LIKE 'Pattern 2';
这篇关于SQL查询以选择与第2列中的两个模式之一匹配的第1列中的值对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！