时间戳

首页 > 解决方案 > SQL Server - 如何从具有多个条件的不同行中获取值？

sql - SQL Server - 如何从具有多个条件的不同行中获取值？

问题描述

我有一个样本表 TempT：

Amount  | ChargeID  | IDPaymentItem | rCode | rCodeAmt | IDPayment | eDate
0.00    | 530064    | 1236182       | PR1   | 251.10   | 769630    | 04/07/2020
0.00    | 530064    | 1236182       | PR45  | 2238.90  | 769630    | 04/07/2020
188.33  | 530064    | 1240623       | CO45  | 2238.90  | 771802    | 04/30/2020
188.33  | 530064    | 1240623       | PR2   | 62.77    | 771802    | 04/30/2020
2238.90 | 530064    | 1240624       |       | 0.00     | 771803    | 04/30/2020

我想创建另一个表，其中 rCode 将显示在金额与 rCodeAmt、ChargeID 和 eDate 匹配的相应行上。

这是我到目前为止的代码，我被卡住了：

DECLARE @From DATETIME
DECLARE @To DATETIME

SET @From = '{Enter starting date range: <!DATE!>}' 
SET @To = '{Enter ending date range: <!DATE!>} 23:59:59' 

SELECT
pi.Amount,
pi.ChargeID,
pi.IDPaymentItem,
pi.PatientID,
SUBSTRING(Value,0,CHARINDEX('=',Value)) AS rCode,
CAST(CAST(SUBSTRING(Value,CHARINDEX('=',Value) + 1,999999) AS FLOAT) AS DECIMAL(30,2)) AS rCodeAmt,
p.IDPayment,
p.eDate,
p.PaymentTypeID,
p.sDate

INTO #TempT

FROM PaymentItem pi
     CROSS APPLY STRING_SPLIT(ReasonCodes,',')
INNER JOIN Payment p on p.IDPayment = pi.PaymentID

WHERE p.eDate BETWEEN @From AND @To

ORDER BY pi.ChargeID

SELECT
pi.Amount,
pi.ChargeID,
pi.IDPaymentItem,
pi.PatientID,
t.rCode,
t.rCodeAmt,
p.IDPayment,
p.eDate,
p.PaymentTypeID,
p.sDate

FROM PaymentItem pi
INNER JOIN Payment p ON p.IDPayment = pi.PaymentID
FULL OUTER JOIN #TempT t ON (t.rCodeAmt = pi.Amount
                             AND t.ChargeID = pi.ChargeID)

WHERE p.eDate BETWEEN @From AND @To

ORDER BY pi.ChargeID,p.IDPayment

DROP TABLE #TempT

这是结果（满足的条件：rCodeAmt 和 ChargeID）。

Amount  | ChargeID  | IDPaymentItem | rCode | rCodeAmt | IDPayment | eDate
0.00    | 530064    | 1236182       |       |          | 769630    | 04/07/2020
188.33  | 530064    | 1240623       |       |          | 771802    | 04/30/2020
2238.90 | 530064    | 1240624       | CO45  | 2238.90  | 771803    | 04/30/2020
2238.90 | 530064    | 1240624       | PR45  | 2238.90  | 771803    | 04/30/2020

我不知道如何插入第三个标准（eDate）。

我希望结果是：

Amount  | ChargeID  | IDPaymentItem | rCode | rCodeAmt | IDPayment | eDate
0.00    | 530064    | 1236182       |       |          | 769630    | 04/07/2020
188.33  | 530064    | 1240623       |       |          | 771802    | 04/30/2020
2238.90 | 530064    | 1240624       | CO45  | 2238.90  | 771803    | 04/30/2020

如果有帮助，这是一个视觉效果：视觉

我的专长也是初学者。谢谢！

附加评论：我尝试在 FROM/JOIN 下面添加第三个条件（eDate），它确实返回三行，但 rCode 值变为空。

FROM PaymentItem pi
INNER JOIN Payment p ON p.IDPayment = pi.PaymentID
FULL OUTER JOIN #TempT t ON (t.rCodeAmt = pi.Amount
                             AND t.ChargeID = pi.ChargeID
                             AND t.eDate = p.eDate)

样本：

Amount  | ChargeID  | IDPaymentItem | rCode | rCodeAmt | IDPayment | eDate
0.00    | 530064    | 1236182       | null  | null     | 769630    | 04/07/2020
188.33  | 530064    | 1240623       | null  | null     | 771802    | 04/30/2020
2238.90 | 530064    | 1240624       | null  | null     | 771803    | 04/30/2020

标签： sqlsql-serverjoin

解决方案

我认为以下内容应该适合您-但是，您在#tempt 中提供的示例数据与屏幕截图不匹配/缺少某些列，因此无法使用这些附加列进行测试...但是以下内容有效退回您的要求！

create table #tempt (
    Amount float
    ,ChargeID int
    ,IDPaymentItem int
    ,rCode varchar(10)
    ,rCodeAmt float
    ,IDPayment int
    ,eDate date
);

insert into #tempt select '0','530064','1236182','PR1','251.1','769630','2020-04-07'
insert into #tempt select '0','530064','1236182','PR45','2238.9','769630','2020-04-07'
insert into #tempt select '188.33','530064','1240623','CO45','2238.9','771802','2020-04-30'
insert into #tempt select '188.33','530064','1240623','PR2','62.77','771802','2020-04-30'
insert into #tempt select '2238.9','530064','1240624','','0','771803','2020-04-30'

----------------
--FINAL SELECT--
----------------
select distinct
    t.Amount
    ,t.ChargeID
    ,t.IDPaymentItem
    --,t.PatientID
    ,t1.rCode
    ,t1.rCodeAmt
    ,t.IDPayment
    ,t.eDate
    --,t.PaymentTypeID
    --,t.sDate

from
    #tempt t
    left outer join #tempt t1
        on t.amount = t1.rCodeAmt
        and t.chargeid = t1.chargeid
        and t.edate = t1.edate

推荐阅读