代码之家 › 专栏 › 技术社区 › RoverRoll

如何获取SQL Server查询中的第一行值

stored-procedures sql-server sql

RoverRoll · 技术社区 · 7 年前

我的要求是允许任何最新的日期后的金额与最新的 paymentid->strong>一起使用。我尝试了很多次,只是为了获得第一行的值。但是我改为获得2行。我尝试使用 rownumber(),但在选择了所有患者姓名后,它不适用。是否有任何一个具有除“Top 1”和“RowNumber()Partition”之外的另一种方法。 my requirement is get one chargeid with allowedamount for the latest postdate with latest paymentid.

选择独特的 TM.[患者姓名]为[患者姓名], [dbo]。[splitString](tm.insplanname,'(')as[insurance], IsNull(tmmm1.ChargeAmount,0)为[已开票金额], 是全部(tmm1.allowedAmount,0)作为allowedAmount, TMM1.日期, TMM1.PayTund, tm.chargeid作为chargeid 来自[MasterReport]作为TM 左外部联接(选择金额(不含(扣款金额,0))作为扣款金额, [查尔盖德] 来自[DBO]。[TransactionMasterReport] 其中transactionType='费用' 按[chargeid]分组为tmmm1 在tm.chargeid=tmmm1.chargeid上左外部联接(选择互异最大(postdate)作为postdate, Max(IsNull(PaymentID,0))作为PaymentID, [查尔盖德] 允许量来自[DBO]。[TransactionMasterReport] 其中([交易类型]='付款' 或[交易类型]='调整') 和允许的金额>=1 和PaymentSource,如“%Primary%” 分组依据[ChargeID], 过期日期允许数量, 付款ID)作为TMM1 在tm上。【chargeid】=tmm1.【chargeid】其中tm.chargeid=4255 TM订购。【ChargeID】 < /代码>当我在左外联接中使用top 1时,我得到0.00的允许量,这是不正确的。行数()但它不适用于选择所有患者姓名后,它没有给出正确的输出。除了前1名和rownumber()分区. 我的要求是获得一个带有allowedAmount的ChargeID,用于最新的PostDate和最新的PaymentID SELECT DISTINCT TM.[PatientName] AS [PATIENT NAME], [dbo].[SplitString](TM.InsPlanName, '(') AS [ Insurance], ISNULL(TMmm1.ChargeAmount, 0) AS [Amount Billed], ISNULL(TMM1.AllowedAmount, 0) AS AllowedAmount, TMM1.PostDate, TMM1.PaymentID, TM.ChargeID AS ChargeID FROM [MasterReport] AS TM LEFT OUTER JOIN (SELECT SUM(ISNULL(ChargeAmount, 0)) AS ChargeAmount, [ChargeID] FROM [dbo].[TransactionMasterReport] WHERE transactiontype = 'Charges' GROUP BY [ChargeID]) AS TMmm1 ON TM.ChargeID = TMmm1.ChargeID LEFT OUTER JOIN (SELECT DISTINCT MAX(PostDate) AS PostDate, MAX(ISNULL(PaymentID, 0)) AS PaymentID, [ChargeID], AllowedAmount FROM [dbo].[TransactionMasterReport] WHERE ([TransactionType] = 'Payments' OR [TransactionType] = 'Adjustments') AND AllowedAmount >= 1 AND PaymentSource LIKE '%Primary%' GROUP BY [ChargeID], PostDate, AllowedAmount, PaymentID) AS TMM1 ON TM.[ChargeID] = TMM1.[ChargeID] WHERE TM.ChargeId = 4255 ORDER BY TM.[ChargeID] 当我在左外联接中使用top 1时,得到0.00的允许量,这是不正确的。

3 回复 | 直到 7 年前

donPablo 7 年前

在您处理语法错误之后,这应该可以工作。我没有与您匹配的表或数据。更改位于第二个leftjoin中,以使用按字段降序排列的行号。您可能需要将整个内容括在另一个select(…)中,并将其中的rn1=1移到下面。

SELECT
DISTINCT
  TM.[PatientName] AS [PATIENT NAME],
  [dbo].[SplitString](TM.InsPlanName, '(') AS [ Insurance],
  ISNULL(TMmm1.ChargeAmount, 0) AS [Amount Billed],
  ISNULL(TMM1.AllowedAmount, 0) AS AllowedAmount,
  TMM1.PostDate,
  TMM1.PaymentID,
  TM.ChargeID AS ChargeID

FROM [MasterReport] AS TM
LEFT OUTER JOIN (SELECT
  SUM(ISNULL(ChargeAmount, 0)) AS ChargeAmount,
  [ChargeID]
FROM [dbo].[TransactionMasterReport]
WHERE transactiontype = 'Charges'
GROUP BY [ChargeID]) AS TMmm1
  ON TM.ChargeID = TMmm1.ChargeID



LEFT OUTER JOIN (SELECT 
             PostDate,
             ISNULL(PaymentID, 0) AS PaymentID,
             ChargeID,
             AllowedAmount,
             Row_Number() Over(Partition By ChargeID  Order By PostDate Desc, PaymentID Desc) as RN1
FROM [dbo].[TransactionMasterReport]
WHERE ([TransactionType] = 'Payments'
    OR [TransactionType] = 'Adjustments')
AND AllowedAmount >= 1
AND PaymentSource LIKE '%Primary%'
) AS TMM1

  ON TM.[ChargeID] = TMM1.[ChargeID]

WHERE  RN1 = 1
  and  TM.ChargeId = 4255

ORDER BY TM.[ChargeID]

Shushil Bohara 7 年前

我们不知道实际情况,但是 suggested above 它应该可以工作,但正如您所说,它再次返回相同的输出,因此使用query作为 subquery 如下图所示并尝试

只从整个输出中返回一条记录

SELECT TOP 1 *
FROM ( <your query> )
ORDER BY postdate, paymentid DESC

归来 top 1 各值 ChargeID

SELECT TOP (1) WITH TIES *
FROM ( <your query> )
GROUP BY ChargeID
ORDER BY postdate, paymentid DESC

an33sh 7 年前

我认为,你应该像

SELECT TOP 1 <your fields>
FROM [MasterReport] AS TM
LEFT OUTER JOIN  <TMmm1> ON TM.ChargeID = TMmm1.ChargeID
LEFT OUTER JOIN  <TMM1> ON TM.[ChargeID] = TMM1.[ChargeID]
WHERE TM.ChargeId = 4255
ORDER BY TMM1.PostDate DESC, TMM1.PaymentID DESC

它只给你一排( TOP 1 )它将是最新的 PostDate 和; PaymentID