如何编写简单的选择查询而不是使用视图? [英] How do I write a simple select query instead of using views?
问题描述
查找在同一天就诊过同一专科的两位不同医生的患者.
Find patients who visited two different doctors of the same specialty in the same day.
示例数据库:点击此处查看 SQL Fiddle 中的示例数据脚本.
CREATE VIEW DistinctVisits AS
SELECT v.vid,v.pid,d.speciality,v.date
FROM Visits v ,Doctors d
WHERE d.did=v.did
GROUP BY v.pid,v.did,v.date;
CREATE VIEW DistinctVisits2 AS
SELECT dv.pid,dv.speciality,dv.date, COUNT(dv.vid) as countv
FROM DistinctVisits dv
GROUP BY dv.pid,dv.speciality,dv.date;
SELECT dv2.pid,dv2.speciality
FROM DistinctVisits2 dv2
WHERE dv2.countv=2;
DROP VIEW DistinctVisits;
DROP VIEW DistinctVisits2;
我如何重复相同的想法，但只针对一个大查询?另一个解决方案也不错，但请先帮助我改进这个.
how do i repeat the same idea but on just one big query? another solutions would be nice as well, but try to help me improve this one first.
推荐答案
说明:
您需要找到在某一天拜访过同一专业的两位不同医生的患者列表.
Explanation:
You need to find the list of patients who had visited two different doctors of the same speciality on a given day.
在此要求中，您的
Patient表成为主表.让我们先查询那个表.In this requirement, your
Patienttable becomes the main table. Let's query that table first.现在我们有了患者名单.我们需要得到他们访问过的医生名单.我们不能简单地将患者表与医生表连接起来，因为没有用于映射数据的列.我们必须使用 Visits 作为中间表
Now we have the list of patients. We need to get the list of doctors they visited. We cannot simply join the Patients table with Doctors table because there is no column to map the data. We have to use Visits as the intermediate table
在 Patient 和 Visits 表之间添加一个
LEFT OUTER JOIN并按 pid 列连接.Add a
LEFT OUTER JOINbetween Patient and Visits table and join by pid column.我们有患者和他们的就诊名单，但现在我们需要获取医生的信息.因此，在 Visits 和 Doctors 表之间添加另一个
LEFT OUTER JOIN并按 did 列加入.We have the patients and the list of their visits but now we need to get the doctors information. So, Add another
LEFT OUTER JOINbetween Visits and Doctors table and join by did column.我们有患者和医生就诊信息.但是，我们只需要患者的姓名、他们就诊的医生的专业以及他们就诊的日期.因此，我们将在 SELECT 子句中添加列
p.pname、d.speciality和v.date并且也在 GROUP BY 子句中.除此之外，我们还需要所有访问计数，但有一个问题.我们只需要 DISTINCT 计数，换句话说，我们需要他们访问过的所有唯一医生的计数.因此，如果患者在某一天拜访了同一位医生两次，则应计为 1.因此，添加DISTINCT在这里会有所帮助.此外，关键是使用正确的列名，在这种情况下 d.did 代表医生.We have the patients and doctor visits information. However, we need only the patient's name, the speciality of the doctor they visited and the date when they visited. So, we will add the columns
p.pname,d.specialityandv.dateto the SELECT clause and also in the GROUP BY clause. In addition to this we, need all the visits count but there is a catch. We need only the DISTINCT count, in other words we need the count of all the unique doctors that they visited. So, if the patient visited the same doctor twice on a give day, it should be counted as 1. So, addingDISTINCTwill help here. Also, the key is to use the correct column name, in this case d.did represents the doctor.我们拥有所需的所有数据，但我们只需要过滤同一天访问过两个不同医生的患者.要做到这一点， HAVING 子句来拯救我们.当您应用 GROUP BY 时，HAVING 是合适的.我们将使用相同的 COUNT(DISTINCT d.did) 来检查计数是否仅与 2 的值匹配.您可以在输出中看到结果.
We have all the data we need but we need filter only the patients who visited two different doctors on the same day. To do that, HAVING clause comes to our rescue. HAVING is appropriate when you apply GROUP BY. We will use the same COUNT(DISTINCT d.did) to check if the count matches only the value of 2. You can see the results in the output.
您不必为插入表中的每个值都指定 INSERT INTO 语句.您可以在括号内将它们组合在一起，并用
逗号将它们分开.最后一条语句应以分号结尾.
You don't have to specify the INSERT INTO statement for every value being inserted into a table. You can group them together within parentheses and separate them by
commas. The last statement should end withsemicolon.
查询使用
INNER JOIN，我认为这更适合您的场景.The query uses
LEFT OUTER JOIN. I used this join to find out all the doctor visits for each patient even if they had never visited a doctor. I just wanted to see the output as I formed the query. You could change this toINNER JOIN, which I think is more appropriate in your scenario.如果您不想显示访问次数，可以将其从 SELECT 子句中删除.
If you don't want to display the visits count, you can remove it from the SELECT clause.
SELECT p.pname , d.speciality , v.date , COUNT(DISTINCT d.did) AS visitcount FROM Patient p LEFT OUTER JOIN Visits v ON v.pid = p.pid LEFT OUTER JOIN Doctors d ON d.did = v.did GROUP BY p.pname , d.speciality , v.date HAVING COUNT(DISTINCT d.did) = 2更适合您的脚本:
SELECT p.pname , d.speciality , v.date FROM Patient p INNER JOIN Visits v ON v.pid = p.pid INNER JOIN Doctors d ON d.did = v.did GROUP BY p.pname , d.speciality , v.date HAVING COUNT(DISTINCT d.did) = 2输出:
PNAME SPECIALITY DATE VISITCOUNT --------- ------------ --------- ----------- Loch Ness Assholes 17/9/2012 2 Loch Ness Orthopedist 13/1/2011 2创建表并插入脚本:
create table InsuranceCompanies ( cid int, cname varchar(20), primary key (cid) ); create table Patient ( pid int, pname varchar(20), age int, cid int, gender char, primary key (pid), constraint foreign key (cid) references InsuranceCompanies (cid) ); create table Doctors ( did int , dname varchar(20), speciality varchar(20), age int, cid int, primary key (did), constraint foreign key (cid) references InsuranceCompanies (cid) ); create table Visits( vid int, pid int, did int, date varchar(20), primary key (vid), constraint foreign key (pid) references Patient (pid) , constraint foreign key (did) references Doctors (did) ); INSERT INTO InsuranceCompanies(cid, cname) VALUES ( 1111, 'Harel Inc' ), ( 2222, 'Clalit Inc' ); INSERT INTO Doctors ( did, dname, speciality, age, cid) VALUES ( 100, 'Jhonny Depp', 'Heart', 42, 1111 ), ( 101, 'Tom Tolan', 'Assholes', 62, 1111 ), ( 105, 'Yom Tov', 'Assholes', 52, 1111 ), ( 102, 'Lauren Jaime', 'Throat', 27, 2222 ), ( 103, 'Gomez Flaurence', 'Legs', 37, 2222 ), ( 106, 'David Harpaz', 'Orthopedist', 37, 2222 ), ( 107, 'David Schwimmer', 'Orthopedist', 37, 2222 ), ( 108, 'Sammy Salut', 'Orthopedist', 37, 1111 ); INSERT INTO Patient ( pid, pname, age, cid,gender) VALUES ( 200, 'Jon Gilmour', 25, 2222, 'm' ), ( 206, 'Bon Gilmour', 30, 2222, 'm' ), ( 205, 'Jon Gilmour', 22, 2222, 'm' ), ( 201, 'Bon Jovy', 21, 2222, 'm' ), ( 202, 'Loch Ness', 17, 2222, 'f' ), ( 203, 'Lilach Sonin', 12, 1111, 'f' ), ( 209, 'Lilach Dba', 34, 1111, 'f' ), ( 210, 'Paulina Daf', 32, 1111, 'f' ), ( 204, 'Gerry Jalor', 23, 1111, 'm' ), ( 208, 'Jerrushalem Jalor', 23, 1111, 'm' ); INSERT INTO Visits ( vid, pid, did, date) VALUES ( 300, 204, 100, '12/12/2012' ), ( 301, 204, 101, '12/12/2012' ), ( 302, 204, 101, '02/01/2012' ), ( 303, 202, 101, '17/09/2012' ), ( 311, 202, 105, '17/09/2012' ), ( 304, 203, 102, '12/12/2011' ), ( 312, 202, 106, '13/06/2012' ), ( 314, 202, 107, '13/01/2011' ), ( 313, 202, 108, '13/01/2011' ), ( 305, 204, 102, '10/10/2011' ), ( 306, 201, 100, '12/01/2012' ), ( 316, 204, 108, '18/05/2012' ), ( 307, 202, 100, '12/07/2012' ), ( 315, 203, 108, '12/07/2012' ), ( 310, 204, 103, '10/04/2012' ), ( 308, 203, 102, '12/12/2011' ), ( 309, 200, 101, '12/12/2012' );这篇关于如何编写简单的选择查询而不是使用视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
