MYSQL select基于select

Question

你能帮上忙我会很感激的..。如果我有下表和示例数据...myGroupTable

group\_Id : user\_Id 

1 : 3 

1 : 7 

1 : 100 

2 : 3 

2 : 7 

2 : 100 

2 : 104 

4 : 42 

4 : 98 

4 : 13

我想要一条sql语句，它可以...返回一个恰好包含指定user_Id的group_Id。例如。是否有user_Id为3、7和100的group_Id的答案: group_id 1。请注意，我不希望它返回group_Id 2，因为它的user_Id也是104……关于J的Kind

Answer 1

这是另一种选择：

SELECT group_id, GROUP_CONCAT(user_id ORDER BY user_id) AS user_id_list
FROM group_user
GROUP BY group_id
HAVING user_id_list = '3,7,100'

Answer 2

SELECT
    group_Id,
    SUM(
        IF user_Id = 3 THEN 1
        ELSEIF user_Id = 7 THEN 2
        ELSEIF user_Id = 100 THEN 4
        ELSE 8
    ) AS bits
FROM myGroupTable
GROUP BY group_Id
HAVING bits=7

这假设你不能对同一个group_Id有重复的user_Ids，例如这永远不会发生：

group     user
   1         3
   1         3

编辑：您可以通过以下方式构建查询：

<?php
$ids = array(3, 7, 100);
$power = 2;
$query = "
    SELECT
    group_Id,
    SUM(
        IF user_Id = " .$ids[0]. " THEN 1 ";
foreach ($id in $ids) { 
    $query .= " ELSEIF user_Id = $id THEN " . $power;
    $power = $power * 2;
}
$query .= " ELSE $power
    ) AS bits
    FROM myGroupTable
    GROUP BY group_Id
    HAVING bits = " . ($power - 1);

Answer 3

另一种解决方案：

从TB1 WHERE group_id NOT IN (SELECT DISTINCT group_id FROM tbl WHERE user_id NOT IN(3,7,100))和user_id IN (3，7,100)中选择group；