22,206
社区成员
发帖
与我相关
我的任务
分享
--有以下2个表 希望 #s表能够按 #plh的plh顺序获取 plh赋值给#s的plh
select '10001' as bh,10 shl,' ' as plh into #s union
select '10002' as bh,20 shl,' ' as plh union
select '10003' as bh,50 shl,' ' as plh union
select '10004' as bh,22 shl,' ' as plh union
select '10005' as bh,33 shl,' ' as plh union
select '10006' as bh,15 shl,' ' as plh union
select '10007' as bh,23 shl,' ' as plh
select '1-1-1' as plh into #plh union
select '1-1-2' as plh union
select '1-2-2' as plh union
select '1-3-2' as plh union
select '1-4-2' as plh union
select '1-1-3' as plh union
select '1-3-4' as plh union
select '1-4-1' as plh union
select '1-5-2' as plh union
select '1-5-3' as plh union
select '1-5-4' as plh union
select '1-6-1' as plh union
select '1-6-2' as plh union
select '1-6-3' as plh union
select '1-6-4' as plh
select * From #s
select * From #plh order by plh
--期望结果
bh shl plh
-----------------------
10001 10 1-1-1
10002 20 1-1-2
10003 50 1-1-3
10004 22 1-2-2
10005 33 1-3-2
10006 15 1-3-4
10007 23 1-4-1
;WITH s
AS
(
select *,ROW_NUMBER()OVER(PARTITION BY qu ORDER BY bh) AS SeqNr From #s WHERE plh=''
),plh
AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY qu ORDER BY a.plh) AS SeqNr FROM #plh AS a WHERE NOT EXISTS(SELECT 1 FROM #s WHERE plh=a.plh)--过滤已取号
)
--这个方法和
select *,ROW_NUMBER()OVER(PARTITION BY qu ORDER BY bh) AS SeqNr into #aaa From #s WHERE plh=''
SELECT *,ROW_NUMBER()OVER(PARTITION BY qu ORDER BY a.plh) AS SeqNr into #bbb FROM #plh AS a WHERE NOT EXISTS(SELECT 1 FROM #s WHERE plh=a.plh)--过滤已取号
--直接写入临时表 功能一样 有啥区别啊
USE tempdb
GO
select '10001' as bh,10 shl,'A' AS qu,CAST(' ' AS VARCHAR(10)) AS plh into #s union
select '10002' as bh,20 shl,'A' AS qu,' ' as plh union
select '10003' as bh,50 shl,'A' AS qu,' ' as plh union
select '10004' as bh,22 shl,'B' AS qu,' ' as plh union
select '10005' as bh,33 shl,'B' AS qu,' ' as plh union
select '10006' as bh,15 shl,'B' AS qu,' ' as plh union
select '10007' as bh,23 shl,'B' AS qu,' ' as plh
select 'A' AS qu,'1-1-1' as plh into #plh union
select 'A' AS qu,'1-1-2' as plh union
select 'A' AS qu,'1-2-2' as plh union
select 'A' AS qu,'1-3-2' as plh union
select 'A' AS qu,'1-4-2' as plh union
select 'A' AS qu,'1-1-3' as plh union
select 'A' AS qu,'1-3-4' as plh union
select 'B' AS qu,'1-4-1' as plh union
select 'B' AS qu,'1-5-2' as plh union
select 'B' AS qu,'1-5-3' as plh union
select 'B' AS qu,'1-5-4' as plh union
select 'B' AS qu,'1-6-1' as plh union
select 'B' AS qu,'1-6-2' as plh union
select 'B' AS qu,'1-6-3' as plh union
select 'B' AS qu,'1-6-4' as plh
GO
;WITH s
AS
(
select *,ROW_NUMBER()OVER(PARTITION BY qu ORDER BY bh) AS SeqNr From #s WHERE plh=''
),plh
AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY qu ORDER BY a.plh) AS SeqNr FROM #plh AS a WHERE NOT EXISTS(SELECT 1 FROM #s WHERE plh=a.plh)--过滤已取号
)
UPDATE s
SET plh=a.plh
FROM plh AS a
WHERE s.qu=a.qu AND s.SeqNr=a.SeqNr
SELECT * FROM #s
/*
10001 10 A 1-1-1
10002 20 A 1-1-2
10003 50 A 1-1-3
10004 22 B 1-4-1
10005 33 B 1-5-2
10006 15 B 1-5-3
10007 23 B 1-5-4
*/
select '10001' as bh,10 shl,'A' AS qu,CAST(' ' AS VARCHAR(10)) AS plh into #s union
select '10002' as bh,20 shl,'A' AS qu,' ' as plh union
select '10003' as bh,50 shl,'A' AS qu,' ' as plh union
select '10004' as bh,22 shl,'B' AS qu,' ' as plh union
select '10005' as bh,33 shl,'B' AS qu,' ' as plh union
select '10006' as bh,15 shl,'B' AS qu,' ' as plh union
select '10007' as bh,23 shl,'B' AS qu,' ' as plh
select 'A' AS qu,'1-1-1' as plh into #plh union
select 'A' AS qu,'1-1-2' as plh union
select 'A' AS qu,'1-2-2' as plh union
select 'A' AS qu,'1-3-2' as plh union
select 'A' AS qu,'1-4-2' as plh union
select 'A' AS qu,'1-1-3' as plh union
select 'A' AS qu,'1-3-4' as plh union
select 'B' AS qu,'1-4-1' as plh union
select 'B' AS qu,'1-5-2' as plh union
select 'B' AS qu,'1-5-3' as plh union
select 'B' AS qu,'1-5-4' as plh union
select 'B' AS qu,'1-6-1' as plh union
select 'B' AS qu,'1-6-2' as plh union
select 'B' AS qu,'1-6-3' as plh union
select 'B' AS qu,'1-6-4' as plh
select * From #s
select * From #plh order by plh
--需要如下 结果 就是被分配的再A区则只能从A区里分配序号 B区只能从B区里取序号
bh shl qu plh
---------------------------------
10001 10 A 1-1-1
10002 20 A 1-1-2
10003 50 A 1-2-2
10004 22 B 1-4-1
10005 33 B 1-5-2
10006 15 B 1-5-3
10006 23 B 1-5-4
select '10001' as bh,10 shl,CAST(' ' AS VARCHAR(10)) AS plh into #s union
select '10002' as bh,20 shl,' ' as plh union
select '10003' as bh,50 shl,' ' as plh union
select '10004' as bh,22 shl,' ' as plh union
select '10005' as bh,33 shl,' ' as plh union
select '10006' as bh,15 shl,' ' as plh union
select '10007' as bh,23 shl,' ' as plh
select '1-1-1' as plh into #plh union
select '1-1-2' as plh union
select '1-2-2' as plh union
select '1-3-2' as plh union
select '1-4-2' as plh union
select '1-1-3' as plh union
select '1-3-4' as plh union
select '1-4-1' as plh union
select '1-5-2' as plh union
select '1-5-3' as plh union
select '1-5-4' as plh union
select '1-6-1' as plh union
select '1-6-2' as plh union
select '1-6-3' as plh union
select '1-6-4' as plh
--select * From #s
--select * From #plh order by plh
;WITH s
AS
(
select *,ROW_NUMBER()OVER(ORDER BY bh)AS SeqNr From #s WHERE plh=''
),plh
AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY a.plh) AS SeqNr FROM #plh AS a WHERE NOT EXISTS(SELECT 1 FROM #s WHERE plh=a.plh)--过滤已取号
)
UPDATE s
SET plh=a.plh
FROM plh AS a
WHERE s.SeqNr=a.SeqNr
SELECT * FROM #s
/*
bh shl plh
10001 10 1-1-1
10002 20 1-1-2
10003 50 1-1-3
10004 22 1-2-2
10005 33 1-3-2
10006 15 1-3-4
10007 23 1-4-1
*/
DROP TABLE #s,#plh