MySQL 中的交集

发布于 2024-08-22 09:56:05 字数 371 浏览 9 评论 0原文

我有两个表,记录和数据。记录有多个字段(名字、姓氏等)。这些字段中的每一个都是存储实际值的数据表的外键。我需要搜索多个记录字段。

下面是一个使用 INTERSECT 的示例查询,但我需要一个可以在 MySQL 中运行的查询。

SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john"
INTERSECT
SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith"

感谢您的任何帮助。

I have two tables, records and data. records has multiple fields (firstname, lastname, etc.). Each of these fields is a foreign key for the data table where the actual value is stored. I need to search on multiple record fields.

Below is an example query using INTERSECT, but I need one that works in MySQL.

SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john"
INTERSECT
SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith"

Thanks for any help.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(9

地狱即天堂 2024-08-29 09:56:05

您可以使用内部联接来过滤在另一个表中具有匹配行的行:

SELECT DISTINCT records.id 
FROM records
INNER JOIN data d1 on d1.id = records.firstname AND data.value = "john"
INNER JOIN data d2 on d2.id = records.lastname AND data.value = "smith"

许多其他替代方案之一是 in 子句:

SELECT DISTINCT records.id 
FROM records
WHERE records.firstname IN (
    select id from data where value = 'john'
) AND records.lastname IN (
    select id from data where value = 'smith'
)

You can use an inner join to filter for rows that have a matching row in another table:

SELECT DISTINCT records.id 
FROM records
INNER JOIN data d1 on d1.id = records.firstname AND data.value = "john"
INNER JOIN data d2 on d2.id = records.lastname AND data.value = "smith"

One of many other alternatives is an in clause:

SELECT DISTINCT records.id 
FROM records
WHERE records.firstname IN (
    select id from data where value = 'john'
) AND records.lastname IN (
    select id from data where value = 'smith'
)
人海汹涌 2024-08-29 09:56:05

我认为这种方法更容易遵循,但有一些与之相关的开销,因为您最初加载了大量重复记录。我在大约有 10000-50000 条记录的数据库上使用它,通常会交叉大约 5 个查询,性能是可以接受的。

您要做的就是“UNION ALL”您想要交叉的每个查询,并查看每次得到的查询。

SELECT * From (

    (Select data1.* From data1 Inner Join data2 on data1.id=data2.id where data2.something=true)
    Union All
    (Select data1.* From data1 Inner Join data3 on data1.id=data3.id where data3.something=false)

) As tbl GROUP BY tbl.ID HAVING COUNT(*)=2 

因此,如果我们在两个查询中获得相同的记录,则其计数将为 2,并且最终的环绕查询将包含它。

I think this method is much easier to follow, but there is a bit of an overhead associated with it because you are loading up lots of duplicate records initially. I use it on a database with about 10000-50000 records and typically intersect about 5 queries and the performance is acceptable.

All you do is "UNION ALL" each of the queries you want to intersect and see which ones you got every time.

SELECT * From (

    (Select data1.* From data1 Inner Join data2 on data1.id=data2.id where data2.something=true)
    Union All
    (Select data1.* From data1 Inner Join data3 on data1.id=data3.id where data3.something=false)

) As tbl GROUP BY tbl.ID HAVING COUNT(*)=2 

So if we get the same record in both queries, it's count will be 2 and the final wrap-around query will include it.

oО清风挽发oО 2024-08-29 09:56:05

使用连接代替:

SELECT records.id
FROM records
JOIN data AS D1 ON records.firstname = D1.id
JOIN data AS D2 ON records.lastname = D2.id
WHERE D1.value = 'john' and D2.value = 'smith'

这里有一些测试数据:

CREATE TABLE records (id INT NOT NULL, firstname INT NOT NULL, lastname INT NOT NULL);
INSERT INTO records (id, firstname, lastname) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 2, 2);

CREATE TABLE data (id INT NOT NULL, value NVARCHAR(100) NOT NULL);
INSERT INTO data (id, value) VALUES
(1, 'john'),
(2, 'smith');

预期结果:

2

测试数据可能对发布者没有用,但对于想要检查解决方案以确保其正常工作的选民,或者想要提交答案的人来说可能有用。他们可以测试自己的答案。

Use joins instead:

SELECT records.id
FROM records
JOIN data AS D1 ON records.firstname = D1.id
JOIN data AS D2 ON records.lastname = D2.id
WHERE D1.value = 'john' and D2.value = 'smith'

Here's some test data:

CREATE TABLE records (id INT NOT NULL, firstname INT NOT NULL, lastname INT NOT NULL);
INSERT INTO records (id, firstname, lastname) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 2, 2);

CREATE TABLE data (id INT NOT NULL, value NVARCHAR(100) NOT NULL);
INSERT INTO data (id, value) VALUES
(1, 'john'),
(2, 'smith');

Expected result:

2

The test data is probably not useful for the poster, but might be useful for voters who want to check solutions to see that they work correctly, or people who want to submit answers so that they can test their own answers.

分分钟 2024-08-29 09:56:05

我来晚了一点,但我认为完全模拟 INTERSECT 的最干净、最好的方法是:

SELECT * FROM
( SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john" ) x1
NATURAL JOIN
( SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith" ) x2

I'm a little late to the party, but I think the cleanest and best way to fully emulate INTERSECT is:

SELECT * FROM
( SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john" ) x1
NATURAL JOIN
( SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith" ) x2
鹿! 2024-08-29 09:56:05

自 2022 年 11 月 10 日起,MySQL 添加了对 INTERSECT< /code>运算符与 版本8.0.31的更新

您现在可以随意使用它:

SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john"
INTERSECT
SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith"

Since 10 November 2022, MySQL has added support to the INTERSECT operator with the updates of version 8.0.31.

You can now feel free to use it:

SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john"
INTERSECT
SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith"
笑梦风尘 2024-08-29 09:56:05

MYSQL 中 INTERSECT 的一般替代是内部联接:

SELECT DISTINCT * FROM 
(SELECT f1, f2, f3... FROM table1 WHERE f1>0)
INNER JOIN
(SELECT f1, f2, f3... FROM table2 WHERE f1>0)
USING(primary_key)

或者针对您的具体情况:

SELECT DISTINCT * FROM 
(SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john") query1
INNER JOIN
(SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith") query2
USING (id)

A general replacement for INTERSECT in MYSQL is inner join:

SELECT DISTINCT * FROM 
(SELECT f1, f2, f3... FROM table1 WHERE f1>0)
INNER JOIN
(SELECT f1, f2, f3... FROM table2 WHERE f1>0)
USING(primary_key)

Or for your case specifically:

SELECT DISTINCT * FROM 
(SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john") query1
INNER JOIN
(SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith") query2
USING (id)
去了角落 2024-08-29 09:56:05

从表 t WHERE NOT EXISTS 中选择 t.id(SELECT t2.id,FROM table2 t2 WHERE t2.id = t1.id)

https://dev.mysql.com/doc/refman/5.7/en/exists-and-not -exists-subqueries.html

SELECT t.id FROM table t WHERE NOT EXISTS (SELECT t2.id, FROM table2 t2 WHERE t2.id = t1.id)

https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

数理化全能战士 2024-08-29 09:56:05

由于Mysql不支持INTERSECT,因此您可能有2种选择:内连接in。这是一个 in 的解决方案:

SELECT records.id FROM records, data 
WHERE data.id = records.firstname AND data.value = "john"
    AND records.id in (SELECT records.id FROM records, data 
    WHERE data.id = records.lastname AND data.value = "smith);

Since Mysql doesn't support INTERSECT, you may have 2 alternatives: inner join and in. This is a solution with in:

SELECT records.id FROM records, data 
WHERE data.id = records.firstname AND data.value = "john"
    AND records.id in (SELECT records.id FROM records, data 
    WHERE data.id = records.lastname AND data.value = "smith);
枕头说它不想醒 2024-08-29 09:56:05

你可以试试这个:

SELECT 
        r.id, GROUP_CONCAT(data.value) AS gcValue
FROM records r
LEFT JOIN data d ON d.id = records.firstname
GROUP BY records.firstname
HAVING FIND_IN_SET('john', gcValue) AND FIND_IN_SET('smith', gcValue); 

但是这个选项没有优化。

You can try this:

SELECT 
        r.id, GROUP_CONCAT(data.value) AS gcValue
FROM records r
LEFT JOIN data d ON d.id = records.firstname
GROUP BY records.firstname
HAVING FIND_IN_SET('john', gcValue) AND FIND_IN_SET('smith', gcValue); 

But there is no optimization on this option.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文