MySQL 中的交集
我有两个表,记录和数据。记录有多个字段(名字、姓氏等)。这些字段中的每一个都是存储实际值的数据表的外键。我需要搜索多个记录字段。
下面是一个使用 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(9)
您可以使用内部联接来过滤在另一个表中具有匹配行的行:
许多其他替代方案之一是
in
子句:You can use an inner join to filter for rows that have a matching row in another table:
One of many other alternatives is an
in
clause:我认为这种方法更容易遵循,但有一些与之相关的开销,因为您最初加载了大量重复记录。我在大约有 10000-50000 条记录的数据库上使用它,通常会交叉大约 5 个查询,性能是可以接受的。
您要做的就是“UNION ALL”您想要交叉的每个查询,并查看每次得到的查询。
因此,如果我们在两个查询中获得相同的记录,则其计数将为 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.
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.
使用连接代替:
这里有一些测试数据:
预期结果:
测试数据可能对发布者没有用,但对于想要检查解决方案以确保其正常工作的选民,或者想要提交答案的人来说可能有用。他们可以测试自己的答案。
Use joins instead:
Here's some test data:
Expected result:
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.
我来晚了一点,但我认为完全模拟 INTERSECT 的最干净、最好的方法是:
I'm a little late to the party, but I think the cleanest and best way to fully emulate
INTERSECT
is:自 2022 年 11 月 10 日起,MySQL 添加了对
INTERSECT< /code>
运算符与 版本8.0.31的更新。
您现在可以随意使用它:
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:
MYSQL 中 INTERSECT 的一般替代是内部联接:
或者针对您的具体情况:
A general replacement for INTERSECT in MYSQL is inner join:
Or for your case specifically:
从表 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
由于Mysql不支持INTERSECT,因此您可能有2种选择:内连接和in。这是一个 in 的解决方案:
Since Mysql doesn't support INTERSECT, you may have 2 alternatives: inner join and in. This is a solution with in:
你可以试试这个:
但是这个选项没有优化。
You can try this:
But there is no optimization on this option.