一道SQL题

Assume we have loaded a flatfile with patient diagnosis data into a table called “patientdata”. The tablestructure is:

Create table patientdata (

Firstname varchar(50),

Lastname varchar(50),

Date_of_birthdatetime,

Medical_record_numbervarchar(20),

Diagnosis_locationvarchar(20),

Diagnosis_datedatetime,

Diagnosis_code  varchar(20))

The data in the flat filelooks like this:

First  Last   date_of_birth Med RecNo   Diag Loc      Diag Date   Diag Code

------------- ------------ ---------- -------------  ----------  ---------

'jane','jones','2/2/2001',  'MRN-11111','MayoClinic', '3/3/2009','diabetes'

'jane','jones','2/2/2001',  'MRN-11111','MayoClinic', '1/3/2009','asthma'

'jane','jones','5/5/1975',  'MRN-88888','LaheyClinic','2/17/2009','flu'

'tom','smith','4/12/2002',  'MRN-22222','LaheyClinic','3/3/2009','diabetes'

'tom','smith','4/12/2002',  'MRN-33333','MayoClinic', '1/3/2009','asthma'

'tom','smith','4/12/2002',  'MRN-33333','MayoClinic', '2/7/2009','asthma'

'jack','thomas','8/10/1991','MRN-44444','MayoClinic', '3/7/2009','asthma'

'jack','thomas','8/10/1991','MRN-44444','LaheyClinic','3/16/2009','flu'

You can assume that no twopatients have the same firstname, lastname, and date of birth combination.

Write a query that shows whichof these patients were diagnosed in more than one location.

This problem has manysolutions, but if you know SQL, you should be able to find one that uses a single query with no subqueries.

这样对吗？

SELECT Firstname, Lastname, Date_of_birth
FROM patientdata
GROUP BY Firstname, Lastname, Date_of_birth
HAVING COUNT(DISTINCT Diagnosis_location > 1);