if object_id('tempdb.dbo.#') is not null drop table #create table #
(
i int identity(0,1),
id as right('0'+ltrim(i/90000+1),2)+right('0'+ltrim(i/3000%30+1),2)+right('0'+ltrim(i/200%15+1),2)+right('0'+ltrim(i/20%10+1),2)+right('0'+ltrim(i%20+1),2),
data varchar(1)
)insert # select 'a'
insert # select 'b'select * from #/*
i id data
----------- -------------------- ----
0 0101010101 a
1 0101010102 b
*/
(
i int identity(0,1),
id as right('0'+ltrim(i/90000+1),2)+right('0'+ltrim(i/3000%30+1),2)+right('0'+ltrim(i/200%15+1),2)+right('0'+ltrim(i/20%10+1),2)+right('0'+ltrim(i%20+1),2),
data varchar(1)
)insert # select 'a'
insert # select 'b'select * from #/*
i id data
----------- -------------------- ----
0 0101010101 a
1 0101010102 b
*/
解决方案 »
- 返回查询一条记录的前10条和后10条记录 包含查询的那条记录的集
- 【求助】SQL Server定义约束
- 数据自动匹配更新的问题
- ?55555555555555还原错了数据库,丢了一个月的数据能不能找回?????
- 请问如何避免向数据库插入重复记录?求完整SQL语句。
- 高手求助: 将 expression 转换为数据类型 smalldatetime 时发生算术溢出错误。语句已终止。
- 米老师,ssis sql 2005 develop 中有么
- 這樣如何統計?
- 请问mysql是不是一定要安装在C? 谢谢
- 2000professional能不能装sqlserver 的服务器端?
- 备份 库中所有的存储过程
- 关于sql server数据库分区的作用?
@i int -- loop
declare @n table (n int)
set @i=1
while @i<100 begin
insert into @n values (@i)
set @i=@i+1
endselect
right(cast(100+t as varchar),2) -- 楼
+ right(cast(100+r as varchar),2) -- 房间
+ right(cast(100+b as varchar),2) -- 架子
+ right(cast(100+x as varchar),2) -- 行
+ right(cast(100+y as varchar),2) -- 列
from
(select t=n from @n where n <= 20) as t, -- 20栋楼
(select r=n from @n where n <= 30) as r, -- 30个房间
(select b=n from @n where n <= 15) as b, -- 15个架子
(select x=n from @n where n <= 10) as x, -- 10行
(select y=n from @n where n <= 20) as y -- 20列
@i int -- loop
declare @n table (n int)
set @i=1
while @i<100 begin
insert into @n values (@i)
set @i=@i+1
enddeclare @rLimit int, @tLimit int, @bLimit int, @xLimit int, @yLimit int
set @tLimit = 3 -- @tLimit栋楼
set @rLimit = 4 -- @rLimit个房间
set @bLimit = 5 -- @bLimit个架子
set @xLimit = 3 -- @xLimit行
set @yLimit = 8 -- @yLimit列select num =
right(cast(100+t as varchar),2) -- 楼
+ right(cast(100+r as varchar),2) -- 房间
+ right(cast(100+b as varchar),2) -- 架子
+ right(cast(100+x as varchar),2) -- 行
+ right(cast(100+y as varchar),2) -- 列
from
(select t=n from @n where n <= @tLimit) as t,
(select r=n from @n where n <= @rLimit) as r,
(select b=n from @n where n <= @bLimit) as b,
(select x=n from @n where n <= @xLimit) as x,
(select y=n from @n where n <= @yLimit) as y
order by num