588
社区成员
发帖
与我相关
我的任务
分享
SELECT DISTINCT
TD.DocEntry ,
TD.LineNum ,
CASE WHEN TD1.BaseEntry = TM.DocEntry
AND TD1.BaseLineNum = TD.LineNum THEN TM1.DocEntry
END AS RKDH ,
CASE WHEN TD1.BaseEntry = TM.DocEntry
AND TD1.BaseLineNum = TD.LineNum THEN TD1.LineNum
END AS HH
FROM ZZ_QGD TM
LEFT JOIN ZZ_QGDA TD ON TD.DocEntry = TM.DocEntry
LEFT JOIN (ZZ_QGDR TM1
INNER JOIN ZZ_QGDRA TD1 ON TD1.DocEntry = TM1.DocEntry) ON TM1.DocEntry = TM.DocEntry
WHERE TM.FKTK <> '现付';
Select Distinct TD.DocEntry,TD.LineNum,
Case When TD1.BaseEntry=TM.DocEntry and TD1.BaseLineNum=TD.LineNum Then TM1.DocEntry end as RKDH,
Case When TD1.BaseEntry=TM.DocEntry and TD1.BaseLineNum=TD.LineNum Then TD1.LineNum end as HH
From ZZ_QGD TM
LEFT JOIN ZZ_QGDA TD on TD.DocEntry=TM.DocEntry
LEFT JOIN ZZ_QGDR TM1 on TM1 --这里on后边没有条件
LEFT JOIN ZZ_QGDRA TD1 on TD1.DocEntry=TM1.DocEntry
Where TM.FKTK <> '现付'
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([DocEntry] int,[LineNum] int,[RKDH] int,[HH] int)
Insert #T
select 10018,6,null,null union all
select 10018,7,10021,9 union all
select 10018,7,null,null union all
select 10018,6,10021,8
Go
--测试数据结束
SELECT DocEntry ,
LineNum ,
RKDH ,
HH
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY DocEntry, LineNum ORDER BY RKDH DESC ) AS num
FROM #T
) t
WHERE num = 1