我现在数据库中有个表主键列是库位的编码
1D03071 第一位代表跨(范围1-4)第二位代表区(范围A-H)3、4位代表行(1-25),五六位代表列(1-15)最后一位表示在1层还是2层
每个区行和列的数量不定,使用sql语句实现将所有的库位编码填到数据库中,我写了个存储过程根本不行,后来网友帮改了,语法通过了但逻辑是不对的,望高手指点
create proc proc_StorageMaker
@Step varchar(1),
@Area varchar(1),
@RowCount int,
@LineCount int
as
declare @RowCountTemp int
declare @LineCountTemp int
set @RowCountTemp = @RowCount
while(@RowCount>0)
begin
while(@LineCount>0)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
select @Step+@Area+case when @RowCount <10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2))+
case when @LineCount <10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2))+'1' end end
if(@RowCount != @RowCountTemp)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
select @Step+@Area+case when @RowCount <10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2)) end+ case when @LineCount <10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2)) end+'2'
end
set @LineCount = @LineCount-1
end
set @RowCount = @RowCount-1
end
1D03071 第一位代表跨(范围1-4)第二位代表区(范围A-H)3、4位代表行(1-25),五六位代表列(1-15)最后一位表示在1层还是2层
每个区行和列的数量不定,使用sql语句实现将所有的库位编码填到数据库中,我写了个存储过程根本不行,后来网友帮改了,语法通过了但逻辑是不对的,望高手指点
create proc proc_StorageMaker
@Step varchar(1),
@Area varchar(1),
@RowCount int,
@LineCount int
as
declare @RowCountTemp int
declare @LineCountTemp int
set @RowCountTemp = @RowCount
while(@RowCount>0)
begin
while(@LineCount>0)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
select @Step+@Area+case when @RowCount <10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2))+
case when @LineCount <10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2))+'1' end end
if(@RowCount != @RowCountTemp)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
select @Step+@Area+case when @RowCount <10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2)) end+ case when @LineCount <10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2)) end+'2'
end
set @LineCount = @LineCount-1
end
set @RowCount = @RowCount-1
end
解决方案 »
- 求Trigger :修改表TB1中的字段F的值时回滚。
- 求一SQL语句,重新生成产品的顺序号
- 大家好,如何半夜12点定时执行一个存储过程,此存储过程的功能是将两个不同的远程服务器的三个表同步
- 求SQL语句。谢谢!
- 求一个比较有难度的SQL语句 当场给分~~
- sql语句的超级简单问题,进来拿分啦!
- 我在我的程序里创建了一个临时表#t,然后我在sql server 2000查询分析器里"select * from #t"确得到提示“对象名 '#t' 无效。”why?
- 请给用友做过数据接口的朋友来看看
- 如何获得SQL SERVER 下一个数据库的所有表名称
- SQL Server 查询好评率问题问题
- [向happyflystone提问] 在函数中使用CTE遇到问题,来请教一下
- sys. 和 dbo.的问题
while a到h as b cast()varchar
while 1到25 as c cast()varchar
while 1到15 as d cast()varchar
while 1到2 as 3 cast()varchar
a+b+right('0'+c,2)+right('0'+d,2)+e
跨是1-4,但是每个跨中有多少区是不固定的,最多是h,有可能只有4个区,
行和列也是不固定的,在sql语句中(sql server)中怎么循环啊
select 1 跨 into #跨
union all select 2
union all select 3
union all select 4select 'A' 区 into #区
union all select 'B'
union all select 'C'
union all select 'D'
union all select 'E'
union all select 'F'
union all select 'G'
union all select 'H'create table #行 (行 int)
declare @i int
set @i=1
while @i<=25
begin
insert #行 (行) values (@i)
set @i=@i+1
endcreate table #列 (列 int)
set @i=1
while @i<=15
begin
insert #列 (列) values (@i)
set @i=@i+1
endselect 1 层 into #层
union all select 2--查询所有库位编码
select cast(跨 as varchar(2))+
cast(区 as varchar(2))+
right('00'+cast(行 as varchar(2)),2)+
right('00'+cast(列 as varchar(2)),2)+
cast(层 as varchar(2)) 库位编码
from #跨,#区,#行,#列,#层 /*结果(共24000个库位编码)
库位编码
----------
1A01011
1A02011
1A03011
1A04011
1A05011
......
4H22152
4H23152
4H24152
4H25152(24000 行受影响)
Go
/*
1D03071
第一位代表跨(范围1-4)
第二位代表区(范围A-H)
3、4位代表行(1-25),
五六位代表列(1-15)最后一位表示在1层还是2层
每个区行和列的数量不定,
使用sql语句实现将所有的库位编码填到数据库中,
我写了个存储过程根本不行,后来网友帮改了,语法通过了但逻辑是不对的,望高手指点
*/
--第1位
--第2位
--第3-4位
--第5-6位
--第7位If Object_id('NoList') Is Not Null --编码表
Drop Table NoList
Create Table NoList
(
id int Identity(1,1),
[No] nvarchar(50) ,
Constraint PK_NoList_id Primary Key(id Asc)
)If Object_id('proc_StorageMaker') Is Not Null
Drop Proc proc_StorageMaker
Go
Create Proc proc_StorageMaker
As
Set Nocount On
Select Top 100 id=Identity(int,1,1) Into #Rows From sys.sysobjects,sys.syscolumns
Insert Into NoList
Select a.N+b.N+c.N+d.N+e.N From
(Select Rtrim(ID) As N From #Rows Where id<=4) a, --第1位
(Select Char(64+id) AS N From #Rows Where ID<=8 ) b, --第2位
(Select Right(Power(10,2)+id,2) As N From #Rows Where ID<=25) c, --第3-4位
(Select Right(Power(10,2)+id,2) As N From #Rows Where ID<=15) d, --第5-6位
(Select Rtrim(ID) As N From #Rows Where ID<=2) e --第7位
Where Not Exists(Select 1 From NoList Where [No]=a.N+b.N+c.N+d.N+e.N)
Drop Table #Rows
GoExec proc_StorageMakerSelect [No] From NoList
/*
No
--------------------------------------------------
1A01011
1A02011
1A03011
1A04011
1A05011
1A06011
1A07011
... ...
4H21152
4H22152
4H23152
4H24152
4H25152(24000 行受影响)
*/
我给定一个跨号比如1,给定一个区号比如“D”,我在给定行的数量15,列的数量12,
通过一个存储过程,我就能生成相应的库位编码
应该是
1D01011
1D01012
1D02011
1D02022
......
1D15121
最后一行没有2层,所以应该有15*12*2-12=348行数据
@跨 varchar(2),
@区 varchar(2),
@行 int,
@列 int
AS
BEGIN
declare @id int
set @id=1
create table #行 (行 int)
declare @i int
set @i=1
while @i<=@行
begin
insert #行 (行) values (@i)
set @i=@i+1
end
create table #列 (列 int)
set @i=1
while @i<=@列
begin
insert #列 (列) values (@i)
set @i=@i+1
end
select 1 层 into #层
union all select 2select distinct @跨+@区+right('00'+cast(行 as varchar(2)),2)+
right('00'+cast(列 as varchar(2)),2)+
case when 行=@行 then '1' else cast(层 as varchar(2)) end 库位编码
from #行,#列,#层
END
go
exec wen_test '1','D',15,12/*结果
1D01011
1D01012
1D01021
1D01022
......
1D15101
1D15111
1D15121
(348 行受影响)
*/