自动分配号码

crazy_boom 2015-12-26 02:45:46
sql2008r2


--有以下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

...全文
112 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2015-12-27
  • 打赏
  • 举报
回复
引用 6 楼 crazy_boom 的回复:
你的那个方法是内存表 我的方法是物理表 可以这样理解吗 ?
这是一种语法不存在内存表说法 公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次。
crazy_boom 2015-12-26
  • 打赏
  • 举报
回复
请教一个问题 大神

;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)--过滤已取号

--直接写入临时表 功能一样 有啥区别啊

crazy_boom 2015-12-26
  • 打赏
  • 举报
回复
你的那个方法是内存表 我的方法是物理表 可以这样理解吗 ?
中国风 2015-12-26
  • 打赏
  • 举报
回复
加上PARTITION BY qu 就行了
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
*/
crazy_boom 2015-12-26
  • 打赏
  • 举报
回复
感谢 roy_88 搞的过程中发现少了一个 区的分别

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


中国风 2015-12-26
  • 打赏
  • 举报
回复
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
中国风 2015-12-26
  • 打赏
  • 举报
回复
在另一贴已回复了

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧