使用4表内连接时如何添加行值 [英] How to add row value when we use 4 table inner join

问题描述

我的查询..............



选择PatientReg.PatientName，PatientReg.Age，PatientReg.ClinicalProb，PatientReg。 Mobile，PatientReg.GuardianName，PatientReg.AdmitTime，PatientReg.DPatientId，

PatientReg.Relation，Bed.BedId，Bed.BedNo，SUM（InTreatment.Total）AS'InTreatmentTotal'，SUM（InTreatment.Price） ）AS'InTreatmentPrice'，PatientReg.P_Id，

EarlyPayment.Amount

来自PatientReg INNER JOIN

Bed ON PatientReg.BedId = Bed.BedId INNER JOIN

InTreatment ON PatientReg.P_Id = InTreatment.PatientId INNER JOIN

EarlyPayment ON PatientReg.P_Id = EarlyPayment.IP_Id

WHERE（PatientReg。 P_Id ='15'）AND（PatientReg.InOut ='I'）

GROUP BY PatientReg.PatientName，PatientReg.Age，PatientReg.ClinicalProb，PatientReg.Mobile，PatientReg.GuardianName，PatientReg.Admi tTime，PatientReg.DPatientId，

PatientReg.Relation，Bed.BedId，Bed.BedNo，PatientReg.P_Id，EarlyPayment.Amount



OutPut ............



Riya 22 trd 68767 hbbb 28-10-2013 D00015 bbbb 8 3 3540 186 15 44444

Riya 22 trd 68767 hbbb 28-10-2013 D00015 bbbb 8 3 3540 186 15 99999





但我想



Riya 22 trd 68767 hbbb 28-10-2013 D00015 bbbb 8 3 3540 186 15 144443

解决方案

选择PatientName，Age，ClinicalProb，Mobile，GuardianName，AdmitTime，DPatientId，

关系，BedId，BedNo，InTreatmentTotal，InTreatmentPrice，P_Id，

来自
（

SELECT PatientReg.PatientName，PatientReg.Age，PatientReg.ClinicalProb，PatientReg.Mobile，PatientReg.GuardianName，PatientReg.AdmitTime，PatientReg.DPatientId，

PatientReg.Relation，Bed.BedId，Bed.BedNo，SUM（InTreatmen t.Total）AS'InTreatmentTotal'，SUM（InTreatment.Price）AS'InTreatmentPrice'，PatientReg.P_Id，

EarlyPayment.Amount

来自PatientReg INNER JOIN
Bed ON PatientReg.BedId = Bed.BedId INNER JOIN

InTreatment ON PatientReg.P_Id = InTreatment.PatientId INNER JOIN

EarlyPayment ON PatientReg.P_Id = EarlyPayment .IP_Id

WHERE（PatientReg.P_Id = '15'）AND（PatientReg.InOut ='I'）

GROUP BY PatientReg.PatientName，PatientReg.Age，PatientReg。 ClinicalProb，PatientReg.Mobile，PatientReg.GuardianName，PatientReg.AdmitTime，PatientReg.DPatientId，

PatientReg.Relation，Bed.BedId，Bed.BedNo，PatientReg.P_Id，EarlyPayment.Amount

）由PatientName，Age，ClinicalProb，Mobile，GuardianName，AdmitTime，DPatientId，

关系，BedId，BedNo，InTreatmentTotal，InTreatmentPrice，P_Id 组BLOCKQUOTE>

My Query..............

SELECT PatientReg.PatientName, PatientReg.Age, PatientReg.ClinicalProb, PatientReg.Mobile, PatientReg.GuardianName, PatientReg.AdmitTime, PatientReg.DPatientId,
PatientReg.Relation, Bed.BedId, Bed.BedNo, SUM(InTreatment.Total) AS 'InTreatmentTotal', SUM(InTreatment.Price) AS 'InTreatmentPrice', PatientReg.P_Id,
EarlyPayment.Amount
FROM PatientReg INNER JOIN
Bed ON PatientReg.BedId = Bed.BedId INNER JOIN
InTreatment ON PatientReg.P_Id = InTreatment.PatientId INNER JOIN
EarlyPayment ON PatientReg.P_Id = EarlyPayment.IP_Id
WHERE (PatientReg.P_Id = '15') AND (PatientReg.InOut = 'I')
GROUP BY PatientReg.PatientName, PatientReg.Age, PatientReg.ClinicalProb, PatientReg.Mobile, PatientReg.GuardianName, PatientReg.AdmitTime, PatientReg.DPatientId,
PatientReg.Relation, Bed.BedId, Bed.BedNo, PatientReg.P_Id, EarlyPayment.Amount

OutPut............

Riya 22 trd 68767 hbbb 28-10-2013 D00015 bbbb 8 3 3540 186 15 44444
Riya 22 trd 68767 hbbb 28-10-2013 D00015 bbbb 8 3 3540 186 15 99999


but I want

Riya 22 trd 68767 hbbb 28-10-2013 D00015 bbbb 8 3 3540 186 15 144443

解决方案

SELECT PatientName, Age, ClinicalProb, Mobile, GuardianName, AdmitTime, DPatientId,
Relation, BedId, BedNo, InTreatmentTotal, InTreatmentPrice, P_Id,
Sum(Amount) from
(
SELECT PatientReg.PatientName, PatientReg.Age, PatientReg.ClinicalProb, PatientReg.Mobile, PatientReg.GuardianName, PatientReg.AdmitTime, PatientReg.DPatientId,
PatientReg.Relation, Bed.BedId, Bed.BedNo, SUM(InTreatment.Total) AS 'InTreatmentTotal', SUM(InTreatment.Price) AS 'InTreatmentPrice', PatientReg.P_Id,
EarlyPayment.Amount
FROM PatientReg INNER JOIN
Bed ON PatientReg.BedId = Bed.BedId INNER JOIN
InTreatment ON PatientReg.P_Id = InTreatment.PatientId INNER JOIN
EarlyPayment ON PatientReg.P_Id = EarlyPayment.IP_Id
WHERE (PatientReg.P_Id = '15') AND (PatientReg.InOut = 'I')
GROUP BY PatientReg.PatientName, PatientReg.Age, PatientReg.ClinicalProb, PatientReg.Mobile, PatientReg.GuardianName, PatientReg.AdmitTime, PatientReg.DPatientId,
PatientReg.Relation, Bed.BedId, Bed.BedNo, PatientReg.P_Id, EarlyPayment.Amount
) a
group by PatientName, Age, ClinicalProb, Mobile, GuardianName, AdmitTime, DPatientId,
Relation, BedId, BedNo, InTreatmentTotal, InTreatmentPrice, P_Id

这篇关于使用4表内连接时如何添加行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！