mirror of
https://gitee.com/coder-xiaomo/leetcode-problemset
synced 2025-09-11 18:31:41 +08:00
133 lines
4.5 KiB
HTML
133 lines
4.5 KiB
HTML
<p>表：<code>patients</code></p>
|
||
|
||
<pre>
|
||
+-------------+---------+
|
||
| Column Name | Type |
|
||
+-------------+---------+
|
||
| patient_id | int |
|
||
| patient_name| varchar |
|
||
| age | int |
|
||
+-------------+---------+
|
||
patient_id 是这张表的唯一主键。
|
||
每一行表示一个患者的信息。
|
||
</pre>
|
||
|
||
<p>表：<code>covid_tests</code></p>
|
||
|
||
<pre>
|
||
+-------------+---------+
|
||
| Column Name | Type |
|
||
+-------------+---------+
|
||
| test_id | int |
|
||
| patient_id | int |
|
||
| test_date | date |
|
||
| result | varchar |
|
||
+-------------+---------+
|
||
test_id 是这张表的唯一主键。
|
||
每一行代表一个 COVID 检测结果。结果可以是阳性、阴性或不确定。
|
||
</pre>
|
||
|
||
<p>编写一个解决方案以找到从 COVID 中康复的患者——那些曾经检测呈阳性但后来检测呈阴性的患者。</p>
|
||
|
||
<ul>
|
||
<li>患者如果 <strong>至少有一次阳性</strong> 检测结果后，在 <strong>之后的日期</strong> 至少有一次 <strong>阴性</strong> 检测结果，则被认为已康复。</li>
|
||
<li>计算从 <strong>首次阳性检测</strong> 结果到 <strong>该阳性检测</strong> 后的 <strong>首次阴性检测结果</strong> 之间的 <strong>康复时间</strong>（以天为单位）</li>
|
||
<li><strong>仅包括 </strong>同时具有阳性及阴性检测结果的患者</li>
|
||
</ul>
|
||
|
||
<p>返回结果表以<em> </em><code>recovery_time</code><em> </em><strong>升序 </strong>排序，然后以<em> </em><code>patient_name</code><em> </em><strong>升序 </strong>排序。</p>
|
||
|
||
<p>结果格式如下所示。</p>
|
||
|
||
<p> </p>
|
||
|
||
<p><strong class="example">示例：</strong></p>
|
||
|
||
<div class="example-block">
|
||
<p><strong>输入：</strong></p>
|
||
|
||
<p>patients 表：</p>
|
||
|
||
<pre class="example-io">
|
||
+------------+--------------+-----+
|
||
| patient_id | patient_name | age |
|
||
+------------+--------------+-----+
|
||
| 1 | Alice Smith | 28 |
|
||
| 2 | Bob Johnson | 35 |
|
||
| 3 | Carol Davis | 42 |
|
||
| 4 | David Wilson | 31 |
|
||
| 5 | Emma Brown | 29 |
|
||
+------------+--------------+-----+
|
||
</pre>
|
||
|
||
<p>covid_tests 表：</p>
|
||
|
||
<pre class="example-io">
|
||
+---------+------------+------------+--------------+
|
||
| test_id | patient_id | test_date | result |
|
||
+---------+------------+------------+--------------+
|
||
| 1 | 1 | 2023-01-15 | Positive |
|
||
| 2 | 1 | 2023-01-25 | Negative |
|
||
| 3 | 2 | 2023-02-01 | Positive |
|
||
| 4 | 2 | 2023-02-05 | Inconclusive |
|
||
| 5 | 2 | 2023-02-12 | Negative |
|
||
| 6 | 3 | 2023-01-20 | Negative |
|
||
| 7 | 3 | 2023-02-10 | Positive |
|
||
| 8 | 3 | 2023-02-20 | Negative |
|
||
| 9 | 4 | 2023-01-10 | Positive |
|
||
| 10 | 4 | 2023-01-18 | Positive |
|
||
| 11 | 5 | 2023-02-15 | Negative |
|
||
| 12 | 5 | 2023-02-20 | Negative |
|
||
+---------+------------+------------+--------------+
|
||
</pre>
|
||
|
||
<p><strong>输出：</strong></p>
|
||
|
||
<pre class="example-io">
|
||
+------------+--------------+-----+---------------+
|
||
| patient_id | patient_name | age | recovery_time |
|
||
+------------+--------------+-----+---------------+
|
||
| 1 | Alice Smith | 28 | 10 |
|
||
| 3 | Carol Davis | 42 | 10 |
|
||
| 2 | Bob Johnson | 35 | 11 |
|
||
+------------+--------------+-----+---------------+
|
||
</pre>
|
||
|
||
<p><strong>解释：</strong></p>
|
||
|
||
<ul>
|
||
<li><strong>Alice Smith (patient_id = 1):</strong>
|
||
|
||
<ul>
|
||
<li>首次阳性检测：2023-01-15</li>
|
||
<li>阳性检测后的首次阴性检测：2023-01-25</li>
|
||
<li>康复时间：25 - 15 = 10 天</li>
|
||
</ul>
|
||
</li>
|
||
<li><strong>Bob Johnson (patient_id = 2):</strong>
|
||
<ul>
|
||
<li>首次阳性检测：2023-02-01</li>
|
||
<li>测试结果不明确：2023-02-05（忽略计算康复时间）</li>
|
||
<li>阳性检测后的首次阴性检测：2023-02-12</li>
|
||
<li>康复时间：12 - 1 = 11 天</li>
|
||
</ul>
|
||
</li>
|
||
<li><strong>Carol Davis (patient_id = 3):</strong>
|
||
<ul>
|
||
<li>检测呈阴性：2023-01-20（在阳性检测前）</li>
|
||
<li>首次阳性检测：2023-02-10</li>
|
||
<li>阳性检测后的首次阴性检测：2023-02-20</li>
|
||
<li>康复时间：20 - 10 = 10 天</li>
|
||
</ul>
|
||
</li>
|
||
<li><strong>没有包含的患者：</strong>
|
||
<ul>
|
||
<li>David Wilson（patient_id = 4）：只有阳性检测，之后没有阴性检测。</li>
|
||
<li>Emma Brown（patient_id = 5）：只有阴性检测，从未有阳性检测。</li>
|
||
</ul>
|
||
</li>
|
||
</ul>
|
||
|
||
<p>输出表以 recovery_time 升序排序，然后以 patient_name 升序排序。</p>
|
||
</div>
|