如何改进此查询以避免使用嵌套视图? [英] How can I improve this query to avoid using nested views?

问题描述

找到拜访了所有与保险公司有关的骨科医生(专科)的患者.

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

数据库: 单击此处以查看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?

到目前为止，这是我要解决的尝试.

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;

推荐答案

熟悉您拥有的数据:

首先要了解的是您拥有的数据.在这种情况下，您有四个表

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

• 保险公司
• 患者
• 医生
• 访问

找到所有拜访与保险公司有关的所有骨科医生(专科)的患者的名单.

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

通常，从总体上看，这些要求可能会有些不知所措.让我们将需求分成较小的组件，以了解您需要做什么.

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:您需要找到专业为骨科医生"的医生列表
2. b部分:查找#1中确定的看过医生的患者的名单.
3. c部分:过滤结果2，以查找共享同一保险公司的患者和医生的列表.
4. 第d部分:发现与所有患者同属一家保险公司的骨科医师中的每位患者.

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.
3. Part c: Filter the result #2 to find the list of patients and doctors who share the same insurance company.
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.

方法:

1. 您需要确定您的主要目标，在这种情况下，您需要确定患者名单.因此，请先查询患者"表.

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和Doctors表之间没有映射.要查找此信息，

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,

在正确的关键字段上将患者"表和访问"表结合起来.

然后将输出与Doctors表的正确键字段连接起来.

如果正确完成了连接，则现在应该具有所有拜访过的患者和医生的列表.如果使用LEFT OUTER JOIN，您甚至会发现从未看过医生的患者.如果使用RIGHT OUTER JOIN，则只会找到去看医生的患者.

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.

现在，您已经拥有所有拜访过的患者和医生.但是，要求是仅查找骨科医师的医生.因此，应用条件对结果进行过滤以仅给出所需的结果.

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.

您现在已经达到了将需求分解为 a部分和 b部分中较小的组成部分的要求.您仍然需要由保险公司对其进行过滤.这是棘手的部分，该要求并不表示您需要显示保险公司，因此我们不必使用表InsuranceCompany.您的下一个问题将是'How am I going to filter the results?'.有效点.找出三个表Patient，Doctor和Visits中的任何一个是否包含保险公司信息. Patient和Doctors具有一个公共字段.加入该公共字段以过滤结果.

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.

查找每个患者曾拜访过的独特的骨科医师的数量.

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

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

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.

您现在应该从子查询中获得字段patient id，patient name，patients visits count和total number of Orthopedists in same insurance company.然后，您可以在patients visits count与total number of Orthopedists in same insurance company匹配的字段上添加外部联接，以过滤此派生表中的结果.我并不是说这是最好的方法.这是我能想到的一种方法.

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.

如果遵循上述逻辑，就应该拥有这个.

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

拜访了所有医生的患者名单

List of patients who have visited all the doctors

仅由骨科医师过滤

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

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.

• 逐步尝试，找到答案后.将其作为单独的答案发布在此处.我将对其进行投票，以弥补您在此问题上获得的所有反对.

我相信您可以轻松做到这一点.

I am confident that you can do this easily.

请随时将您的问题发布为comments to this answer，其他人也将很高兴为您提供帮助.

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

我提供了实现此逻辑的多种方法之一.我相信，有很多方法可以更好地实现这一目标.

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.

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

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.

这篇关于如何改进此查询以避免使用嵌套视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！