在带有绑定的SQL中使用联接

Question

我对SQL很陌生，我有一个关于联接的问题。

问题是这样的，有两个表，第一个表存储关于患者的数据，在病人表中有一个名为字段的属性，存储病人接受治疗的医学字段。第二个表名为Doctors，，这里有一个名为Specialization的属性，存储医生专门处理的医学字段。

医学领域，如心脏病学、病毒学等。

可以有更多的医生在同一专业执业。

如果我要在Doctors.Specialization和Patients.Field的基础上加入这些表，并且限制每个医生最多需要5个病人，那么查询是什么呢？

SELECT *
FROM Patients
inner join Doctors on Patients.Diagnosis = Doctors.Specialization;

Answer 1

我会这样解决的：

1. 使用专门化和诊断列连接这两个表，
2. 使用DENSE_RANK()分析数据对医生和病人进行专业化排序。病人的等级必须在一个范围内：
   • 下界(排外)是：(医生等级- 1) *

    - If doctor's rank is 1, then it's 0.
    - If doctor's rank is 2, then it's 5.

- upper bound (inclusive) is: doctors' rank \* 5.

    - If doctor's rank is 1, then it's 5.
    - If doctor's rank is 2, then it's 10.

WITH base AS (
  SELECT d.specialization,
         d.id AS doctor_id,
         d.name AS doctor_name,
         p.id AS patient_id,
         p.name AS patient_name,

         -- Rank doctors by specialization.
         DENSE_RANK() OVER (
            PARTITION BY d.specialization
            ORDER BY d.id
         ) AS doc_spec_rank,

         -- Rank patients by specialization
         DENSE_RANK() OVER (
            PARTITION BY d.specialization
            ORDER BY p.id
         ) AS patient_spec_rank
  
  FROM doctors d
  INNER JOIN patients p
    ON d.specialization = p.diagnosis
)

SELECT *
FROM base
WHERE (
  (doc_spec_rank - 1) * 5 < patient_spec_rank
  AND doc_spec_rank * 5 >= patient_spec_rank
)
ORDER BY specialization, doc_spec_rank, patient_spec_rank
;

由于您没有提供rbdms和测试数据，所以我冒昧地在Oracle 18c中创建了一个示例模式。

下面是对模式和解决方案的修改：https://dbfiddle.uk/4_kikOO7