如何改进此查询以避免使用嵌套视图？

Find patients who visited all orthopedists (specialty) associated with their insurance companies.

查找访问过与保险公司相关的所有骨科医生(专科)的患者。

Database: Click here to view the sample data script in SQL Fiddle.

数据库:单击此处查看SQL Fiddle中的示例数据脚本。

CREATE VIEW Orthos AS
SELECT  d.cid,d.did
FROM    Doctors d
WHERE d.speciality='Orthopedist';

CREATE VIEW OrthosPerInc AS
SELECT  o.cid, COUNT(o.did) as countd4i
FROM Orthos o
GROUP BY o.cid;

CREATE VIEW OrthoVisitsPerPat AS
SELECT v.pid,COUNT(o.did) as countv4d
FROM Orthos o,Visits v,Doctors d
WHERE o.did=v.did and d.did=o.did
GROUP BY v.pid,d.cid;

SELECT  p.pname,p.pid,p.cid
FROM  OrthoVisitsPerPat v, OrthosPerInc i,Patient p
WHERE i.countd4i = v.countv4d and p.pid=v.pid and p.cid=i.cid;

DROP VIEW IF EXISTS Orthos,OrthosPerInc,OrthoVisitsPerPat;

How can i write it on one query?

我怎么能在一个查询上写它?

Attempt:

So far, here is my attempt at getting this resolved.

到目前为止,我试图解决这个问题。

SELECT  p.pid,p.pname,p.cid,COUNT(v.did)
FROM Visits v 
JOIN Doctors d ON v.did=d.did
JOIN Patient p ON p.pid=v.pid
WHERE d.cid=p.cid and d.speciality="Orthopedist"
GROUP BY p.pid,p.cid;

INTERSECT 

SELECT  p.pid,d.cid,COUNT(d.did)
FROM Doctors d 
JOIN Patient p ON p.cid=d.cid
WHERE d.speciality='Orthopedist'
GROUP BY d.cid;

3 个解决方案

#1

Familiarize with the data that you have:

The first key thing is to understand what data you have. Here in this case, you have four tables

第一个关键是要了解您拥有的数据。在这种情况下,您有四个表

• InsuranceCompanies
• Patient
• Doctors
• Visits

Your goal:

Find the list of all the patients who visited all orthopedists (specialty) associated with their Insurance Companies.

查找访问与其保险公司相关的所有骨科医生(专科)的所有患者的列表。

Let's take a step back and analyze it in smaller pieces:

Generally, the requirements might be a bit overwhelming when you look at them on the whole. Let's split the requirements into smaller components to understand what you need to do.

一般来说,当你整体看它们时,要求可能有点压倒性。让我们将需求分成更小的组件,以了解您需要做什么。

1. Part a: You need to find the list of doctors, whose speciality is 'Orthopedist'

第一部分:你需要找到医生名单,其专长是'骨科医生'

2. Part b: Find the list of patients who visited doctors identified in #1.

第二部分:找到访问#1中确定的医生的患者名单。

3. Part c: Filter the result #2 to find the list of patients and doctors who share the same insurance company.

c部分:过滤结果#2,找到共享同一保险公司的患者和医生名单。

4. Part d: Find out that the patients who visited each one of those Orthopedists who belong to the same insurance company as the patient do.

第d部分:找出访问与患者属于同一保险公司的每位骨科医生的患者。

How to approach:

1. You need to identify your main goal, here in this case to identify the list of patients. So, query the Patient table first.

   您需要确定您的主要目标,在这种情况下,以确定患者列表。因此,首先查询Patient表。

2. You have the patients, actually all of them but we need to find which of these patients visited the doctors. Let's not worry about whether the doctor is an Orthopedist or not. We just need the list of patients and the doctors they have visited. There is no mapping between Patient and Doctors table. To find out this information,

   你有患者,实际上所有患者,但我们需要找到哪些患者去看医生。我们不担心医生是否是骨科医生。我们只需要患者名单和他们访问过的医生。患者和医生表之间没有映射。要了解这些信息,

   Join the Patient table with Visits table on the correct key field.

   使用正确的关键字段上的Visits表加入Patient表。

   Then join the output with the Doctors table on the correct key field.

   然后将输出与Doctors表连接到正确的关键字段。

3. If you have done the join correctly, you should now have the list of all the patients and the doctors that they have visited. If you used LEFT OUTER JOIN, you will find even the patients who had never visited a doctor. If you used RIGHT OUTER JOIN, you will find only the patients who visited a doctor.

   如果您已正确完成连接,您现在应该拥有所有患者和他们访问过的医生的列表。如果您使用LEFT OUTER JOIN,您甚至会发现从未去过医生的患者。如果您使用RIGHT OUTER JOIN,您将只找到去看医生的患者。

4. Now, you have all the patients and the doctors whom they have visited. However, the requirement is to find only the doctors who are Orthopedists. So, apply the condition to filter the result to give only the desired result.

   现在,您有所有患者和他们访问过的医生。但是,要求只找到骨科医生。因此,应用条件过滤结果只给出所需的结果。

5. You have now achieved the requirements as split into smaller components in part a and part b. You still need to filter it by the insurance companies. Here is the tricky part, the requirement doesn't say that you need to display the insurance company, so we don't have to use the table InsuranceCompanies. Your next question will 'How am I going to filter the results?'. Valid point. Find out if any of the three tables Patient, Doctor and Visits contain the insurance company information. Patient and Doctors have a common field. Join that common field to filter the result.

   您现在已经达到了要求,即在a部分和b部分中分成更小的组件。您仍然需要由保险公司过滤它。这里是棘手的部分,要求并不是说你需要展示保险公司,所以我们不必使用保险公司表。您的下一个问题是“我将如何过滤结果?”。有效点。找出患者,医生和访问这三个表中是否包含保险公司信息。患者和医生有共同的领域。加入该公共字段以过滤结果。

6. Find the count of unique Orthopedists that each patient has visited.

   找出每位患者访问过的独特骨科医生的数量。

7. Here is the part that can be done in many ways, one of the way of doing this would be to add a sub query that would be your fourth column in the output. This sub query would query the table Doctors and filter by speciality = 'Orthopedist'. In addition to that filter, you also have to filter by matching the insurance company on the inner table with the insurance company id on the Patients table that is on the main query. This subquery will return the count of all the Orthopedists for insurance company id that matches the patient's data.

   以下是可以通过多种方式完成的部分,其中一种方法是添加一个子查询,该查询将是输出中的第四列。该子查询将查询表Doctors并按special ='Orthopedist'过滤。除了该过滤器之外,您还必须通过将内部表上的保险公司与主查询上的患者表上的保险公司ID进行匹配来进行过滤。该子查询将返回与患者数据匹配的保险公司ID的所有骨科医生的计数。

8. You should now have the fields patient id, patient name, patients visits count and the total number of Orthopedists in same insurance company from the sub query. You can then add an outer join that will filter the results from this derived table on the fields where patients visits count matches with total number of Orthopedists in same insurance company. I am not saying this is the best approach. This is one approach that I can think of.

   您现在应该从子查询中获得患者ID,患者姓名,患者就诊次数和同一保险公司的骨科医生总数字段。然后,您可以添加一个外部联接,该联接将在患者访问次数与同一保险公司的骨科医生总数匹配的字段中筛选此派生表的结果。我不是说这是最好的方法。这是我能想到的一种方法。

9. If you follow the above logic, you should have this.

   如果你遵循上面的逻辑,你应该有这个。

List of patients who have visited all the doctors

访问过所有医生的患者名单

Filtered by only doctors, whose are Orthopedists

仅由医生过滤,他们是骨科医生

Filtered by patients and doctors sharing the same insurance company information.

由患者和医生过滤共享相同的保险公司信息。

Again, the whole output is then filtered by the two count fields found inside the derived table output.

同样,整个输出然后由派生表输出中找到的两个计数字段过滤。

The ball is in your court:

• Try it step by step and once you find the answer. Post it here as a separate answer. I will upvote it to compensate for all the downvotes that you got on this question.

一旦找到答案,一步一步尝试。将其作为单独的答案发布在此处。我会赞成它来弥补你在这个问题上得到的所有挫折。

I am confident that you can do this easily.

我相信你可以轻松地做到这一点。

If you stumble...

Don't hesitate to post your questions as comments to this answer, Others and I will be glad to assist you.

不要犹豫,将您的问题作为评论发布到这个答案,其他人和我将很乐意为您提供帮助。

Disclaimer

I have provided one of the many ways how this logic can be implemented. I am sure that there are many ways to implement this in a far better manner.

我已经提供了如何实现这种逻辑的众多方法之一。我确信有很多方法可以更好地实现这一点。

Outcome:

Please refer @Ofek Ron's answer for the correct query that produces the desired output. I didn't write any part of the query. It was all OP's effort.

请参阅@Ofek Ron的答案,以获得产生所需输出的正确查询。我没有写任何查询的部分。这完全是OP的努力。

#2

@Siva's Explanation :

@Siva的解释:

this is the resulting code of parts 1-5:

这是第1-5部分的结果代码:

SELECT *
FROM Patient p
JOIN Visits v ON v.pid=p.pid
JOIN Doctors d ON d.did=v.did and d.cid=p.cid
WHERE d.speciality="Orthopedist"

applying part 6:

适用第6部分:

SELECT p.pid,COUNT(d.did)
FROM Patient p
JOIN Visits v ON v.pid=p.pid
JOIN Doctors d ON d.did=v.did and d.cid=p.cid
WHERE d.speciality="Orthopedist"
GROUP BY p.pid

applying the rest: Click here to view the demo in SQL Fiddle.

应用其余部分:单击此处查看SQL Fiddle中的演示。

SELECT p.pid,p.cid,COUNT(DISTINCT d.did) as c
FROM Patient p
JOIN Visits v ON v.pid=p.pid
JOIN Doctors d ON d.did=v.did and d.cid=p.cid
WHERE d.speciality="Orthopedist"
GROUP BY p.pid
HAVING (p.cid,c) IN 
  (SELECT d.cid,COUNT(DISTINCT d.did)
  FROM Doctors d
  WHERE d.speciality="Orthopedist"
  GROUP BY d.cid);

#3

Maybe something like this:

也许是这样的:

SELECT
    p.pname,
    p.pid,
    p.cid
FROM
    Patient AS p
    JOIN
    (
        SELECT v.pid,COUNT(o.did) as countv4d
        FROM    Doctors d
        JOIN    Visits v ON o.did=v.did
        WHERE d.speciality='Orthopedist'
        GROUP BY v.pid,d.cid;
    ) AS v
    ON p.pid=v.pid
    JOIN 
    (
        SELECT  o.cid, COUNT(o.did) as countd4i
        FROM    Doctors d
        WHERE d.speciality='Orthopedist'
        GROUP BY o.cid;
    ) AS i
    ON p.cid=i.cid
WHERE
    i.countd4i = v.countv4d