具有多个列的 MySQL 子语句
给出一个假的例子，其中存在联系人，并且每个联系人可以有一个或零个短信记录；我可以做到这一点
SELECT Contact.id, Contact.name, Sms.provider, Sms.number FROM Contact, Sms WHERE Sms.contactId = Contact.id.
，但即使没有该联系人的短信号码，它也需要显示一行。
我知道有两种方法可以实现这一目标。一种是两个选择的 UNION
(SELECT Contact.id, Contact.name, Sms.provider, Sms.number FROM Contact, Sms WHERE Sms.contactId = Contact.id) UNION (SELECT id, name, null provider, null number FROM Contact WHERE (SELECT 1 FROM Sms WHERE contactId = Contact.id) IS NULL).
，另一种是两个子语句
SELECT id, name, (SELECT provider FROM Sms WHERE contactId = Contact.id) provider, (SELECT number FROM Sms WHERE contactId = Contact.id) number FROM Contact
UNION 方法需要遍历数据库两次，而子语句方法需要对同一个表进行两次查找。
我经常遇到这种情况，我更喜欢将所有代码都放在 MySQL 中，而不是使用软件代码来完成此操作。使用 MySQL 通常会更高效、更简单。我处理的许多实际情况都涉及大型数据库。
有没有一种方法可以通过一个查询从相关表中获取两个字段？
Given a fake example where there are contacts and for each contact there can be one or zero Sms records; I can do this
SELECT Contact.id, Contact.name, Sms.provider, Sms.number FROM Contact, Sms WHERE Sms.contactId = Contact.id.
But I it needs to show a row even if there is no SMS number for that contact.
I know of two ways to accomplish this. One is with a UNION of two selects
(SELECT Contact.id, Contact.name, Sms.provider, Sms.number FROM Contact, Sms WHERE Sms.contactId = Contact.id) UNION (SELECT id, name, null provider, null number FROM Contact WHERE (SELECT 1 FROM Sms WHERE contactId = Contact.id) IS NULL).
and another is two substatements
SELECT id, name, (SELECT provider FROM Sms WHERE contactId = Contact.id) provider, (SELECT number FROM Sms WHERE contactId = Contact.id) number FROM Contact
The UNION method requires going through the database twice and the substatements method requires two lookups on the same table.
I run into this often and I prefer to have all the code in MySQL and not to work with the software code to accomplish this. Working with MySQL usually turns out to be more efficient as well as simpler. Many of the real situations I work with involve large databases.
Is there a way to get two fields from a related table with one query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
然后使用
LEFT OUTER JOIN，即使右表中没有对应的行，它也会从左表中返回一行。当您仅用逗号列出表时，学习始终使用 JOIN 语法而不是伪内连接是个好主意。
Then use a
LEFT OUTER JOIN, which returns a row from the left table even if there is no corresponding row in the right table.It's a good idea to learn to always use JOIN syntax instead of the pseudo-inner-join when you just list tables with commas.