27,580
社区成员
发帖
与我相关
我的任务
分享
SELECT 'A' AS PART_NO ,1 AS RID,REQ_QTY=10 INTO #A
UNION ALL
SELECT 'A' AS PART_NO ,2 AS RID,REQ_QTY=27
UNION ALL
SELECT 'A' AS PART_NO ,3 AS RID,REQ_QTY=28
---
SELECT 'A' AS PART_NO,FID=1 ,1 AS RID,REQ_QTY=10 INTO #B
UNION ALL
SELECT 'A' AS PART_NO,FID=1 ,2 AS RID,REQ_QTY=10
UNION ALL
SELECT 'A' AS PART_NO,FID=2 ,2 AS RID,REQ_QTY=5
UNION ALL
SELECT 'A' AS PART_NO,FID=2 ,3 AS RID,REQ_QTY=5
UNION ALL
SELECT 'A' AS PART_NO,FID=3 ,1 AS RID,REQ_QTY=5
select ID=ROW_NUMBER()OVER(PARTITION BY A.PART_NO ORDER BY A.RID)
,*,ASS_QTY=0
from #a a
inner join #b b on a.part_no=b.part_no
and a.rid=b.rid
order by a.part_no,a.rid
select ID=ROW_NUMBER()OVER(PARTITION BY A.PART_NO ORDER BY A.RID)
,*,ASS_QTY=CASE WHEN b.FID=B.RID THEN B.REQ_QTY ELSE 0 END
from #a a
inner join #b b on a.part_no=b.part_no
and a.rid=b.rid
order by a.part_no,a.rid
/*
ID PART_NO RID REQ_QTY PART_NO FID RID REQ_QTY ASS_QTY
-------------------- ------- ----------- ----------- ------- ----------- ----------- ----------- -----------
1 A 1 10 A 1 1 10 10
2 A 1 10 A 3 1 5 0
3 A 2 27 A 1 2 10 0
4 A 2 27 A 2 2 5 5
5 A 3 28 A 2 3 5 0
*/
?