时间戳

首页 > 解决方案 > 在多对多 SQL 表上精确匹配的 Where 语句

sql - 在多对多 SQL 表上精确匹配的 Where 语句

问题描述

我正在尝试构建一个 SQL 语句来搜索两个具有多对多关系的表。

问题：使用 SQL 语句搜索具有精确宝石的产品。

例如，在下表中，我需要一个语句来搜索仅包含红宝石和翡翠石的产品。在我所有的尝试中，我都得到了戒指和项链，因为它们都有红宝石和祖母绿，尽管项链还有一颗额外的石头。它应该只提供 Ring 产品。

我需要一种在石桌上实现 AND 运算符的方法，以便结果包含具有确切石头的产品。请帮忙。

桌石

s_id s_name
1 红宝石
2 翠
3 缟玛瑙

餐桌产品

p_id p_name
1 戒指
2 项链
3 吊坠

关系表 - product_stone

p_s_id p_id s_id
1 1 1
1 1 2
1 2 1
1 2 2
1 2 3
1 3 3

标签： sql

解决方案

这是一个关系划分问题。我们需要找到由我们的列表“划分”的两个表的交叉连接，没有余数，即stone在product.

我们将假设 p_id 和 s_id 是唯一的：

;WITH StonesToFind AS (    -- we could also use a table variable etc here
    SELECT *
    FROM stone
    WHERE s_name IN ('Ruby','Emerald')
)
SELECT p.p_name
FROM product AS p    -- let's get all products...
JOIN product_stone AS ps ON ps.p_id = p.p_id   -- ...cross join all their stones
LEFT JOIN StonesToFind AS s ON s.s_id = ps.s_id    -- they may have stones in the list
GROUP BY p.p_id, p_name
HAVING COUNT(CASE WHEN s.s_id IS NULL THEN 1 END) = 0
-- the number of non matching stones in product must be zero
    AND COUNT(*) = (SELECT COUNT(*) FROM StonesToFind);
-- the total number of stones must be the same as the list

推荐阅读