SQL连接6个表
SQL连接6个表
提问于 2011-07-21 14:11:49
请看一下image.There是与appointTable和appointID相关的5个表。现在我需要的所有数据都有相同的约会id..what应该是连接查询吗?有没有人可以帮我？
下面是生成的查询(我使用的是左外部连接)
SELECT dbo.Appointment.appointment_id, dbo.Appointment.patient_id, dbo.PatientInvestigaiton.investigation_name, dbo.PatientInvestigaiton.investigation_id,
dbo.PatientTreatmentMedicine.medecine_id, dbo.PatientTreatmentMedicine.medicinename, dbo.PatientTreatmentMedicine.medicinetype,
dbo.PatientFindings.finding_id, dbo.PatientFindings.finding_value, dbo.PatientAdvice.advice_description, dbo.PatientCC.cc_value, dbo.PatientCC.cc_id,
dbo.PatientDiagonosis.diagonosis_name, dbo.PatientDiagonosis.diagonosis_id
FROM dbo.Appointment LEFT OUTER JOIN
dbo.PatientInvestigaiton ON dbo.Appointment.appointment_id = dbo.PatientInvestigaiton.appointment_id LEFT OUTER JOIN
dbo.PatientTreatmentMedicine ON dbo.Appointment.appointment_id = dbo.PatientTreatmentMedicine.appointment_id LEFT OUTER JOIN
dbo.PatientFindings ON dbo.Appointment.appointment_id = dbo.PatientFindings.appointment_id LEFT OUTER JOIN
dbo.PatientDiagonosis ON dbo.Appointment.appointment_id = dbo.PatientDiagonosis.appointment_id LEFT OUTER JOIN
dbo.PatientCC ON dbo.Appointment.appointment_id = dbo.PatientCC.appointment_id LEFT OUTER JOIN
dbo.PatientAdvice ON dbo.Appointment.appointment_id = dbo.PatientAdvice.appointment_id
where dbo.Appointment.appointment_id='46';回答 3
Stack Overflow用户
回答已采纳
发布于 2011-07-21 15:21:52
由于appointmnent_id是Appointment的主键，因此该表与所有6个表都具有1:N关系。
这就是连接到这6个表将产生具有重复数据的多行的情况，这就像一个Cartesian Product。例如，如果(只有一个id=46)，有：
- 3行
PatientInvestigation - 6行
PatientTreatmentMedicine - 4行
PatientFindings<代码>H216H1172行<代码>D18<代码>H1202行<代码>D21<代码>H222H1235行<代码>D24<代码>H225<代码>F226
您将在结果集中得到3x6x4x2x2x5 = 1440行，而您只需要3+6+4+2+2+5 (+1) = 23行。这比所需的行数多60倍(列数也多得多)。
如果对每个查询中的(6个)表中的一个表执行6单独查询(以及从基表Appointment获取数据的另一个查询)，效果会更好。和在应用程序代码中组合了这6个查询的结果。基本查询和要连接到第一个表的查询的示例：
基表
SELECT
a.appointment_id,
a.patient_id
FROM
Appointment AS a
WHERE
a.appointment_id = 46Join-1到PatientInvestigation
SELECT
pi.investigation_name,
pi.investigation_id
FROM
Appointment AS a
JOIN
PatientInvestigation AS pi
ON pi.appointment_id = a.appointment_id
WHERE
a.appointment_id = 46Stack Overflow用户
发布于 2011-07-21 14:59:12
SELECT
Appointment.appointment_id,
Appointment.patient_id,
PatientInvestigaiton.investigation_name,
PatientInvestigaiton.investigation_id,
PatientTreatmentMedicine.medecine_id,
PatientTreatmentMedicine.medicinename,
PatientTreatmentMedicine.medicinetype,
PatientFindings.finding_id,
PatientFindings.finding_value,
PatientAdvice.advice_description,
PatientCC.cc_value,
PatientCC.cc_id,
PatientDiagonosis.diagonosis_name,
PatientDiagonosis.diagonosis_id
FROM
Appointment
LEFT OUTER JOIN PatientInvestigaiton ON Appointment.appointment_id = PatientInvestigaiton.appointment_id AND Appointment.appointment_id='46'
LEFT OUTER JOIN PatientTreatmentMedicine ON Appointment.appointment_id = PatientTreatmentMedicine.appointment_id
LEFT OUTER JOIN PatientFindings ON Appointment.appointment_id = PatientFindings.appointment_id
LEFT OUTER JOIN PatientDiagonosis ON Appointment.appointment_id = PatientDiagonosis.appointment_id
LEFT OUTER JOIN PatientCC ON Appointment.appointment_id = PatientCC.appointment_id
LEFT OUTER JOIN PatientAdvice ON Appointment.appointment_id = PatientAdvice.appointment_idStack Overflow用户
发布于 2011-07-21 14:23:36
SELECT {TABLE1}.appointment_id,{OTHER FIELDS} FROM {TABLE1}
JOIN {TABLE2} ON {TABLE1}.appointment_id = {TABLE2}.appointment_id
JOIN {TABLE3} ON {TABLE1}.appointment_id = {TABLE3}.appointment_id
JOIN {TABLE4} ON {TABLE1}.appointment_id = {TABLE4}.appointment_id
JOIN {TABLE5} ON {TABLE1}.appointment_id = {TABLE5}.appointment_id
JOIN {TABLE6} ON {TABLE1}.appointment_id = {TABLE6}.appointment_id
JOIN {TABLE7} ON {TABLE1}.appointment_id = {TABLE7}.appointment_id
JOIN {TABLE8} ON {TABLE1}.appointment_id = {TABLE8}.appointment_id;试试这个：
SELECT
dbo.Appointment.appointment_id, dbo.Appointment.patient_id,
dbo.PatientInvestigaiton.investigation_name, dbo.PatientInvestigaiton.investigation_id,
dbo.PatientTreatmentMedicine.medecine_id, dbo.PatientTreatmentMedicine.medicinename, dbo.PatientTreatmentMedicine.medicinetype,
dbo.PatientFindings.finding_id, dbo.PatientFindings.finding_value,
dbo.PatientAdvice.advice_description,
dbo.PatientCC.cc_value, dbo.PatientCC.cc_id,
dbo.PatientDiagonosis.diagonosis_name, dbo.PatientDiagonosis.diagonosis_id
FROM
dbo.Appointment
LEFT JOIN dbo.PatientInvestigaiton
ON dbo.Appointment.appointment_id = dbo.PatientInvestigaiton.appointment_id
LEFT JOIN dbo.PatientTreatmentMedicine
ON dbo.Appointment.appointment_id = dbo.PatientTreatmentMedicine.appointment_id
LEFT JOIN dbo.PatientFindings
ON dbo.Appointment.appointment_id = dbo.PatientFindings.appointment_id
LEFT JOIN dbo.PatientDiagonosis
ON dbo.Appointment.appointment_id = dbo.PatientDiagonosis.appointment_id
LEFT JOIN dbo.PatientCC
ON dbo.Appointment.appointment_id = dbo.PatientCC.appointment_id
LEFT JOIN dbo.PatientAdvice
ON dbo.Appointment.appointment_id = dbo.PatientAdvice.appointment_id
WHERE
dbo.Appointment.appointment_id='46';页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/6771975
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号