结合这4个SQL函数

Question

我有以下4种SQL功能，它们可以查找病人的程序和与其相关的费用。

计算程序：

SELECT provider.Abbr, procedurelog.ClinicNum Clinic, procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)- IFNULL(SUM(claimproc.WriteOff),0) Production, procedurelog.ProcNum, provider.ProvNum
FROM patient
INNER JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus='2' AND procedurelog.ProcDate >= '2021-06-10' AND procedurelog.ProcDate <= '2021-06-10'
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum AND claimproc.Status='7'
INNER JOIN provider ON procedurelog.ProvNum=provider.ProvNum AND provider.ProvNum IN (2,10,11)
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
GROUP BY procedurelog.ProcNum

计算调整(如果有)

SELECT provider.Abbr, adjustment.ClinicNum Clinic, adjustment.AdjAmt AdjAmt, adjustment.AdjNum, provider.ProvNum
FROM adjustment
INNER JOIN patient ON adjustment.PatNum=patient.PatNum
INNER JOIN definition ON adjustment.AdjType=definition.DefNum
INNER JOIN provider ON adjustment.ProvNum=provider.ProvNum AND provider.ProvNum IN (2,10,11)
WHERE adjustment.AdjDate >= '2021-06-10' AND adjustment.AdjDate <= '2021-06-10'

计算核销

SELECT provider.Abbr,claimproc.ClinicNum Clinic,- SUM(claimproc.WriteOff) WriteOff,claimproc.ClaimNum, claimproc.ClaimProcNum AS 'ProcNum', provider.ProvNum
FROM claimproc
LEFT JOIN patient ON claimproc.PatNum = patient.PatNum
LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum
LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
LEFT JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum
LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
WHERE claimproc.Status IN (1,4,0) AND claimproc.ProvNum IN (2,10,11) AND (claimproc.WriteOff > '.0001' OR claimproc.WriteOff < -.0001) AND claimproc.ProcDate >= '2021-06-10' AND claimproc.ProcDate <= '2021-06-10'
GROUP BY claimproc.ClaimProcNum

这些查询是单独编写的，因为在后端，服务器将执行计算并获得总产量。但是，现在我必须在一个查询中完成所有这些工作。

我的问题是，我不知道如何将以下内容结合起来：

生产金额(从过程tables)

• Adjustment金额(AdjAmt)从调整(如果有)，可以是blank)

• Writeoffs (从核销表)

用这个公式

Total Production = Production + Adjustments - Writeoffs

提供程序编号是相同的，因为我试图根据提供程序计算产品。

我认为我的问题是，我已经迷失在“连接”，我不知道从哪里开始合并它们和计算。

我该怎么做呢？

Answer 1

您可以使用CTE连接这些查询，而不会在连接丛林中丢失。

使用您提供的有限信息，这是使用CTE的基本查询。

WITH 
  production as (
  SELECT provider.Abbr, procedurelog.ClinicNum Clinic, procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)- IFNULL(SUM(claimproc.WriteOff),0) Production, provider.ProvNum
  FROM patient
  INNER JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus='2' AND procedurelog.ProcDate >= '2021-06-10' AND procedurelog.ProcDate <= '2021-06-10'
  LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum AND claimproc.Status='7'
  INNER JOIN provider ON procedurelog.ProvNum=provider.ProvNum AND provider.ProvNum IN (2,10,11)
  INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
  GROUP BY provider.Abbr, procedurelog.ClinicNum, provider.ProvNum
  ),
  adjustment as (
    SELECT provider.Abbr, adjustment.ClinicNum Clinic, sum(adjustment.AdjAmt) AdjAmt, provider.ProvNum
    FROM patient
    INNER JOIN adjustment ON adjustment.PatNum=patient.PatNum
    INNER JOIN definition ON adjustment.AdjType=definition.DefNum
    INNER JOIN provider ON adjustment.ProvNum=provider.ProvNum AND provider.ProvNum IN (2,10,11)
    WHERE adjustment.AdjDate >= '2021-06-10' AND adjustment.AdjDate <= '2021-06-10'
    GROUP BY provider.Abbr, procedurelog.ClinicNum, provider.ProvNum
  ),
  writeoff as (
    SELECT provider.Abbr,claimproc.ClinicNum Clinic,- SUM(claimproc.WriteOff) WriteOff, provider.ProvNum
    FROM provider
    INNER JOIN claimproc ON provider.ProvNum = claimproc.ProvNum
    INNER JOIN patient ON claimproc.PatNum = patient.PatNum
    LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum
    LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
    LEFT JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum
    LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
    WHERE claimproc.Status IN (1,4,0) AND claimproc.ProvNum IN (2,10,11) AND (claimproc.WriteOff > '.0001' OR claimproc.WriteOff < -.0001) AND claimproc.ProcDate >= '2021-06-10' AND claimproc.ProcDate <= '2021-06-10'
    GROUP BY provider.Abbr,claimproc.ClinicNum, provider.ProvNum, procedurelog.ProcNum
  )
SELECT producttion.Abbr, production.ProvNum, production.ClinicNum, production.Production, adjustment.AdjAmt, writeoff.WriteOff
FROM production 
LEFT JOIN adjustment ON producttion.Abbr = adjustment.Abbr AND production.ClinicNum = adjustment.ClinicNum AND production.ProvNum = adjustment.ProvNum
LEFT JOIN writeoff ON producttion.Abbr = writeoff.Abbr AND production.ClinicNum = writeoff.ClinicNum AND production.ProvNum = writeoff.ProvNum

注:未测试