mySQL:从多行中获取所有where的值 - mySQL: get values from multiple lines with all wheres

我有下表：

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `left_id` int(11) NOT NULL DEFAULT '0',
  `right_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `left_id`, `right_id`) VALUES (2, 111, 90),
   (3, 111, 91),(4, 111, 92),(5, 111, 93),(6, 111, 94),(7, 111, 95),
   (8, 222, 90),(9, 222, 91),(10, 222, 92),(11, 222, 93),(12, 222, 94),
   (13, 222, 95),(14, 333, 90),(15, 333, 91),(16, 333, 92),(17, 333, 93),
   (18, 333,94),(19, 333, 100);

看起来像

id | left_id | right_id 
2  | 111     | 90
3  | 111     | 91
4  | 111     | 92
5  | 111     | 93
6  | 111     | 94
7  | 111     | 95
8  | 222     | 90
9  | 222     | 91
10 | 222     | 92
11 | 222     | 93
12 | 222     | 94
13 | 222     | 95
14 | 333     | 90
15 | 333     | 91
16 | 333     | 92
17 | 333     | 93
18 | 333     | 94
19 | 333     | 100

left_id是"新闻"的记录，right_id记录新闻记录所属的类别。

现在我想要所有的left_id（distinct），它们具有精确/所有的right_ids 90&（91或92）&93&94&95

长句：给我（区别）left_id，其中right_id是

(90 & 91 & 93 & 94 & 95)
OR
(90 & 92 & 93 & 94 & 95)

结果必须是111和222；而不是333，因为333具有NOT 95。

您可以将GROUP_CONCAT()与REGEXP一起使用，以便匹配精确的值：

SELECT
    left_id,
    GROUP_CONCAT(DISTINCT right_id ORDER BY right_id) gc
FROM
    test
GROUP BY
    left_id
HAVING
    gc REGEXP '90.+(91|92).+(93).+(94).+(95)'

输出为：

+---------+-------------------+
| left_id | gc                |
+---------+-------------------+
|     111 | 90,91,92,93,94,95 |
|     222 | 90,91,92,93,94,95 |
+---------+-------------------+
2 rows in set

我认为这个查询独立于id中的任何排序和任何可能的重复条目：

SELECT  left_id
FROM    `test`
WHERE   right_id IN (90,91,93,94,95)
GROUP BY left_id
HAVING  COUNT(DISTINCT right_id) = 5
UNION 
SELECT  left_id
FROM    `test`
WHERE   right_id IN (90,92,93,94,95)
GROUP BY left_id
HAVING   COUNT(DISTINCT right_id) = 5

我认为您可以使用group_concat的属性，并检查结果是否与您提供的字符串相同。。

select distinct left_id, group_concat(right_id) from test 
having group_concat(right_id) = ('90,91,93,94,95')
   or group_concat(right_id) = ('90,92,93,94,95') 
group_by left_id

最终将字符串的格式调整为group_concat 的格式化结果

SELECT a.* -- [OR DISTINCT a.left_id] 
  FROM test a
  JOIN
     ( SELECT x.left_id 
         FROM test x 
        WHERE x.right_id IN(90,93,94,95) 
        GROUP 
           BY x.left_id HAVING COUNT(*) = 4
     ) b
    ON b.left_id = a.left_id
 WHERE a.right_id IN (91,92);

试试这个

select left_id, group_concat(right_id) value
from test 
group by 1
having value = ('90,91,93,94,95')
   or value = ('90,92,93,94,95')

您可以使用位掩码。您在集合中找到位置p，将值1向左移动p次并向右移动一次。第一个位置产生二进制0001，第二个位置产生0010，第三个位置产生0100 aso。通过二进制OR将其聚合。

现在，您可以轻松地构建条件。CCD_ 13意味着掩码中设置的每个比特也是聚合设置的。CCD_ 14意味着掩码的至少一个比特被集合设置。

SELECT
  `left_id`,                        # 1  2  4  8  16 32
   BIT_OR(1<<FIND_IN_SET(`right_id`, '90,91,92,93,94,95')>>1) `right_or`
FROM
  `ids`
GROUP BY
  `left_id`
HAVING   1|8|16|32 = (1|8|16|32) & `right_or`
   AND   (2|4) & `right_or`
;

与条件91或95相同：

SELECT
  `left_id`,
  BIT_OR(1<<FIND_IN_SET(`right_id`, '90,91,92,93,94,95')>>1) `right_or`
FROM
  `ids`
GROUP BY
  `left_id`
HAVING    1|4|8|16 = (1|4|8|16) & `right_or`
   AND    (2|32) & `right_or`
;

当然，您可以预先计算常数，例如1|4|8|16=29。我想展示一个可读的例子。