MySQL：返回所有条件(但没有其他条件)为true的所有结果

utugiqy6 于 5个月前发布在 Mysql

关注(0)|答案(3)|浏览(47)

有两个表：

Packages

id     name
1      red
2      blue
3      yellow

Contents

packageid      item           size
1              square         A
1              circle         B
1              triangle       C
2              square         A
2              circle         B
3              square         A

字符串
如果我们查询{ item:square, size:A }，我们只需要{ packages.id:3 }
如果我们查询{ item:square, size:A }和{ item:circle, size:B }，则只需要{ packages.id:2 }
如果我们查询{ item:square, size:A }、{ item:circle, size:B }和{ item:triangle, size:C }，则只需要{ packages.id:1 }
如果有多个完全匹配的软件包，我们将选择所有软件包。
这似乎可行，但并不十分优雅：

SELECT 
p.id,
p.name,
c.item,
c.size

FROM Packages p
LEFT JOIN Contents c ON (
  c.packageid=p.id
)

WHERE (
    ( p.id IN ( SELECT packageid FROM Contents WHERE item='square' AND size='A' ) 
    AND 
    ( p.id IN ( SELECT packageid FROM Contents WHERE item='circle' AND size='B' ) 
)
GROUP BY p.id 
HAVING ( SELECT COUNT(*) FROM Contents WHERE packageid=p.id ) = 2;

型

mysql

来源：https://stackoverflow.com/questions/77649450/mysql-return-all-results-where-all-conditions-but-no-other-are-true

3条答案

按热度按时间

wqlqzqxt1#

使用EXISTS，您将获得想要的结果
Exist是检查是否存在具有所需值的值的最快方法。
在poacage_id，item和size上的索引将增加性能

SELECT 
    p.id, p.name, c.item, c.size
FROM
    Packages p
        LEFT JOIN
    Contents c ON (c.packageid = p.id)
WHERE

(SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'square' AND size = 'A'))
AND (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'circle' AND size = 'B')) 
  AND  (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'triangle' AND size = 'C')) 
  AND (SELECT COUNT(*) FROM Contents WHERE packageid = p.id) = 3;

字符串
| ID|名称|项目|大小|
| --|--|--|--|
| 1 |红色|三角形|C|
| 1 |红色|圈|B|
| 1 |红色|平方|一|

SELECT
p.id, p.name, c.item, c.size
FROM
    Packages p
        LEFT JOIN
    Contents c ON (c.packageid = p.id)
WHERE

(SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'square' AND size = 'A'))
AND (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'circle' AND size = 'B')) 
  AND (SELECT COUNT(*) FROM Contents WHERE packageid = p.id) = 2;

型
| ID|名称|项目|大小|
| --|--|--|--|
| 2 |蓝色|圈|B|
| 2 |蓝色|平方|一|
fiddle

赞(0）回复(0）举报 5个月前

z6psavjg2#

主要条件是根据给定条件获取与行数精确匹配的程序包。例如，如果传递了两组条件，则应收到程序包2，其中包含两行：

with filter as (
  select 'square' item, 'A' size union all
  select 'circle', 'B'
),
filter_with_count as (
  select *, count(*) over() as cnt
  from filter
),
cte as (
  select p.id, p.name, c.item, c.size,
         count(p.id) over(partition by p.id) as cnt
  from Packages p
  inner join Contents c on c.packageid = p.id
)
select c.id, c.name, c.item, c.size
from cte c
inner join filter_with_count f 
           on c.item = f.item 
           and c.size = f.size
           and c.cnt = f.cnt;

字符串
说明：
这些条件将作为第一个CTE上的数据集传递。
第二个CTE使用窗口函数COUNT()对行进行计数。
第三个CTE用于合并两个表，并计算每个包的总行数。
最后，为了获得所需的结果，我们必须按项目、大小和计数将筛选器行与组合数据联接起来。
对于这种情况

('square', 'A') and ('circle', 'B')

型
结果如下：

id  name    item    size
2   blue    square  A
2   blue    circle  B

型
Demo here

赞(0）回复(0）举报 5个月前

izj3ouym3#

您可以将GROUP_CONCAT与包含所需项目的排序列表的字符串一起使用：

select packageid
from contents
group by packageid
having group_concat(concat(item, '|', size) order by item, size separator ',') = 
  'circle|B,square|A';

字符串
演示：https://dbfiddle.uk/IUBFzsHZ

赞(0）回复(0）举报 5个月前