SQL查询以选择与第2列中的两个模式之一匹配的第1列中的值对 [英] SQL query to select pairs of values in a column 1 which match either one of two patterns in column 2

问题描述

我的数据如下：

|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列中的值对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！