blocks|key|1560926|text|使用左连接|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1560927|FROM+PERSON
LEFT+JOIN+PersonContact+ON+person.personID+=+PersonContactID+|code-block|syntax|javascript|1560928|这将在没有联系人的情况下提供空值。|1560929|根据您的SQL类型，您可以使用某种形式的NULL替换来格式化这些NULL。例如，在MSSQL中|1560930|ISNULL(PersonContact.FirstName,+'(unknown)')|1560931|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Using a LEFT JOIN

<pre><code>FROM PERSON
LEFT JOIN PersonContact ON person.personID = PersonContactID 
</code></pre>

This will provide nulls where no contact is available.

Depending on your variety of SQL you can format those NULLS using some form of NULL replacement.
eg, in MSSQL

<pre><code>ISNULL(PersonContact.FirstName, '(unknown)')
</code></pre>

blocks|key|1555806|text|使用LEFT+JOIN获取所有人员和任何可用的联系人，并使用COALESCE将任何空值替换为“UNKNOWN”。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1555807|Select+Person.FirstName,+Person.LastName,+Person.PhoneNumber,+
++++COALESCE(PersonContact.FirstName,+'UNKNOWN')+AS+FirstName,
++++COALESCE(PersonContact.Lastname,+'UNKNOWN')+AS+LastName
From+Person
LEFT+OUTER+Join+PersonContact
++++On+person.personID+=+PersonContactID|code-block|syntax|javascript|1555808|entityMap^0|2|9|U|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Use a <code>LEFT JOIN</code> to get all Persons, and any Contacts that are available, and use <code>COALESCE</code> to replace any NULL values with 'UNKNOWN'.

<pre><code>Select Person.FirstName, Person.LastName, Person.PhoneNumber, 
 COALESCE(PersonContact.FirstName, 'UNKNOWN') AS FirstName,
 COALESCE(PersonContact.Lastname, 'UNKNOWN') AS LastName
From Person
LEFT OUTER Join PersonContact
 On person.personID = PersonContactID
</code></pre>

blocks|key|1555810|text|使用ISNULL()和左外部联接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1555811|SELECT+Person.FirstName+++++
,isnull(Person.LastName,+'Unknown')+as+LastName
,Person.PhoneNumber+++++
,PersonContact.FirstName+++++
,PersonContact.Lastname+
FROM+Person+LEFT+OUTER+JOIN+PersonContact+ON+person.personID+=+PersonContactID+|code-block|syntax|javascript|1555812|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use ISNULL() and a LEFT OUTER JOIN:

<pre><code>SELECT Person.FirstName 
,isnull(Person.LastName, 'Unknown') as LastName
,Person.PhoneNumber 
,PersonContact.FirstName 
,PersonContact.Lastname 
FROM Person LEFT OUTER JOIN PersonContact ON person.personID = PersonContactID 
</code></pre>

blocks|key|4234729|text|您希望在这里使用左连接；因此如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4234730|Select+Person.FirstName,
+++++++Person.LastName,
+++++++Person.PhoneNumber,
+++++++ISNULL(PersonContact.FirstName,+'Unknown')+AS+FirstName,
+++++++ISNULL(PersonContact.Lastname,+'Unknown')+AS+LastName
From+Person+LEFT+OUTER+Join+PersonContact
++++++On+person.personID+=+PersonContactID|code-block|syntax|javascript|4234731|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You want a left join here; so something like this:

<pre><code>Select Person.FirstName,
 Person.LastName,
 Person.PhoneNumber,
 ISNULL(PersonContact.FirstName, 'Unknown') AS FirstName,
 ISNULL(PersonContact.Lastname, 'Unknown') AS LastName
From Person LEFT OUTER Join PersonContact
 On person.personID = PersonContactID
</code></pre>

I hope the table names don't matter, because they're based on my database. I will try to keep it as simple as possible.

This is what I need: Provide a list of Orthopaedic specialists with their assistants' name, and contact information. If the doctor does not have an assistant, put "Unknown" on the report for Last Name.
Personal information is in a table called <code>Person</code>. This includes contact information (<code>PhoneNumber</code>). Each doctor has an ID number called <code>PersonID</code>
Assistant name (<code>FirstName</code>,<code>LastName</code>) is in a table called <code>PersonContact</code>.

Here's What I have so far:

<pre><code>SELECT Person.FirstName
 ,Person.LastName
 ,Person.PhoneNumber
 ,PersonContact.FirstName
 ,PersonContact.Lastname
FROM Person
INNER JOIN PersonContact ON person.personID = PersonContactID
</code></pre>

How can I make it that doctors who don't have assistants show up with "<code>Unknown</code>" for their assistant's names?

EDIT: I've tried every suggestion here, but I'm getting straight "NULL" or "UNKNOWN" for every row. I need it to say "UNKNOWN" when there is no entry for Personcontact.Firstname and/or Personcontact.Lastname.

SQL: Need assistance with this query

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我希望表名无关紧要，因为它们基于我的数据库。我会尽量保持简单。这就是我需要的:提供一份骨科专家的名单，以及他们的助手的名字和联系方式。如果医生没有助手，在“姓氏”报告中写上“未知”。个人信息位于一个名为Person的表中。这包括联系信息(PhoneNumber)。每个医生都有一个名为PersonID助手的ID号，名字(...

问SQL:需要有关此查询的帮助
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL:需要有关此查询的帮助EN