MySQL中的相交 [英] INTERSECT in MySQL

问题描述

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

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.

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

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"

感谢您的帮助.

推荐答案

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

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"

许多其他选择之一是 in 子句:

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'
)

这篇关于MySQL中的相交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！