SQL 一对多匹配多侧中的一侧 by ALL

发布于 2024-07-06 15:22:32 字数 582 浏览 6 评论 0原文

在下面的一对多中

CREATE TABLE source(id int, name varchar(10), PRIMARY KEY(id));
CREATE TABLE params(id int, source int, value int);

，params.source 是 source.id 的外键

INSERT INTO source values(1, 'yes');
INSERT INTO source values(2, 'no');

INSERT INTO params VALUES(1,1,1);
INSERT INTO params VALUES(2,1,2);
INSERT INTO params VALUES(3,1,3);

INSERT INTO params VALUES(4,2,1);
INSERT INTO params VALUES(5,2,3);
INSERT INTO params VALUES(6,2,4);

如果我有一个参数值列表（例如 [1,2,3]），我如何找到具有所有值的所有源在 SQL 的列表中（来源 1，“是”）？

谢谢

原文

In the following one to many

CREATE TABLE source(id int, name varchar(10), PRIMARY KEY(id));
CREATE TABLE params(id int, source int, value int);

where params.source is a foreign key to source.id

INSERT INTO source values(1, 'yes');
INSERT INTO source values(2, 'no');

INSERT INTO params VALUES(1,1,1);
INSERT INTO params VALUES(2,1,2);
INSERT INTO params VALUES(3,1,3);

INSERT INTO params VALUES(4,2,1);
INSERT INTO params VALUES(5,2,3);
INSERT INTO params VALUES(6,2,4);

If i have a list of param values (say [1,2,3]), how do I find all the sources that have ALL of the values in the list (source 1, "yes") in SQL?

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄离 2024-07-13 15:22:32

SELECT s.*
FROM source AS s
 JOIN params AS p ON (p.source = s.id)
WHERE p.value IN (1,2,3)
GROUP BY s.id
HAVING COUNT(DISTINCT p.value) = 3;

您需要 DISTINCT 因为您的 params.value 不会被阻止重复。

SELECT s.*
FROM source AS s
 JOIN params AS p ON (p.source = s.id)
WHERE p.value IN (1,2,3)
GROUP BY s.id
HAVING COUNT(DISTINCT p.value) = 3;

You need the DISTINCT because your params.value is not prevented from having duplicates.

回复收藏 0 原文

街道布景 2024-07-13 15:22:32

编辑进行修改以处理给定源的值可能多次出现的情况。

试试这个：

SELECT
    *
FROM
    source
WHERE
    (
        SELECT COUNT(DISTINCT value)
        FROM params
        WHERE params.source = source.id
          AND params.value IN (1, 2, 3)
    ) = 3

您也可以将其重写为 GROUP BY：

SELECT
    source.*
FROM
    source
    INNER JOIN params ON params.source = source.id
WHERE
    params.value IN (1, 2, 3)
GROUP BY
    source.id,
    source.name
HAVING
    COUNT(DISTINCT params.value) = 3

Edit Modified to handle case where there can be multiple occurances of the value for a given source.

Try this:

SELECT
    *
FROM
    source
WHERE
    (
        SELECT COUNT(DISTINCT value)
        FROM params
        WHERE params.source = source.id
          AND params.value IN (1, 2, 3)
    ) = 3

You can rewrite it to a GROUP BY as well:

SELECT
    source.*
FROM
    source
    INNER JOIN params ON params.source = source.id
WHERE
    params.value IN (1, 2, 3)
GROUP BY
    source.id,
    source.name
HAVING
    COUNT(DISTINCT params.value) = 3

回复收藏 0 原文

~没有更多了~