请教高手一个房间排列的存储过程或SQl！

Question

有一个房间表。
CREATE TABLE room (code char(10),floor char(10))
INSERT INTO room VALUES ('201', '2')
INSERT INTO room VALUES ('202', '2')
INSERT INTO room VALUES ('203', '2')
INSERT INTO room VALUES ('301', '3')
INSERT INTO room VALUES ('302', '3')
INSERT INTO room VALUES ('401', '4')
要根据以上数据画房间的列表图，已知每行可画5个房间
希望能用sql 或者存储过程实现下面的数据
'201','2'
'202','2'
'203','2'
'虚拟','2'
'虚拟','2'
'301','3'
'302','3'
'虚拟','3'
'虚拟','3'
'虚拟','3'
'401','4'
'虚拟','4'
'虚拟','4'
'虚拟','4'
'虚拟','4'如果有了上述的数据，我就可以要达到下面的图形效果
201  202    203    '虚拟'  '虚拟'
301  302   '虚拟'  '虚拟'  '虚拟'
401 '虚拟' '虚拟'  '虚拟'  '虚拟'
--上面的图形只是意思而已，关键是需要数据
我用了一下比较笨的方法，虽然实现了，但是固定插入的
怎么样才能算法出可以自动插入行数，或者有更好的办法
create  PROCEDURE dbo.p_room
AS
CREATE TABLE #rm (code varchar(10) NULL, floor varchar(10) NULL)
begin
insert into #rm SELECT * from room  
--插入虚拟不显示房间
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','2' )  
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','2' )  
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','3' )  
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','3' )  
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','3' )  
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','4' )  
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','4' )  
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','4' )  
INSERT INTO #rm ( code,   floor ) vALUES ( '虚拟','4' )
endSELECT * from #rm order by floor
 请教如何用sql 语法或者存储过程来实现这样的结果？谢谢大家

Answer 1

create table room (code char(10),floor char(10)) 
insert into room values ( '201 ', '2 ') 
insert into room values ( '202 ', '2 ') 
insert into room values ( '203 ', '2 ') 
insert into room values ( '301 ', '3 ') 
insert into room values ( '302 ', '3 ') 
insert into room values ( '401 ', '4 ') select left(code,1) as id
into #p
from room
group by left(code,1)
select top 5 identity(int,1,1) as id
into #q
from sysobjects
select cast(a.id as varchar(10)) + '0' + cast(b.id as varchar(10)) as id
into #r
from #p as a inner join #q as b on 1=1insert into room 
select '虚拟',left(id,1)
from #r as a left join room as b on a.id=b.code
where b.code is nullselect * 
from room
order by floor,codedrop table room,#p,#q,#r
/*201         2         
202         2         
203         2         
虚拟        2         
虚拟        2         
301         3         
302         3         
虚拟        3         
虚拟        3         
虚拟        3         
401         4         
虚拟        4         
虚拟        4         
虚拟        4         
虚拟        4         */

Answer 2

create TABLE room (code char(10),floor char(10)) 
INSERT INTO room VALUES ( '201 ', '2 ') 
INSERT INTO room VALUES ( '202 ', '2 ') 
INSERT INTO room VALUES ( '203 ', '2 ') 
INSERT INTO room VALUES ( '301 ', '3 ') 
INSERT INTO room VALUES ( '302 ', '3 ') 
INSERT INTO room VALUES ( '401 ', '4 ') 
go
create proc InsertData
@RowDataNum int
as
declare @sql varchar(1000)
declare @floor varchar(20),@Num intdeclare cur cursor for select floor, count(1) num from room group by floor
open cur
fetch next from cur into @floor,@Num
while @@fetch_status=0
begin
if @RowDataNum-@Num !=0
  set @sql=replicate('insert room select ''虚拟'','''+rtrim(@floor)+''';',@RowDataNum-@Num)
exec(@sql)
fetch next from cur into @floor,@Num
end
select * from room order by floor,code
close cur
deallocate curgo
InsertData 5
--result
/*code       floor      
---------- ---------- 
201        2         
202        2         
203        2         
虚拟         2         
虚拟         2         
301        3         
302        3         
虚拟         3         
虚拟         3         
虚拟         3         
401        4         
虚拟         4         
虚拟         4         
虚拟         4         
虚拟         4         
*/

Answer 3

--创建测试
CREATE TABLE #room (code char(10),floor char(10))
INSERT INTO #room VALUES ('201','2')
INSERT INTO #room VALUES ('202','2')
INSERT INTO #room VALUES ('203','2')
INSERT INTO #room VALUES ('301','3')
INSERT INTO #room VALUES ('302','3')
INSERT INTO #room VALUES ('401','4')--每行可画5个房间
select top 5 id=identity(int,1,1) into #temp from syscolumnsselect code=isnull(c.code,'虚拟'),a.floor
from
(select distinct floor from #room) a
cross join
#temp b
left join
(select *,id=(select count(1) from #room where floor=a.floor and code<=a.code) from #room a) c
on a.floor=c.floor and b.id=c.id/*
code       floor      
---------- ---------- 
201        2         
202        2         
203        2         
虚拟         2         
虚拟         2         
301        3         
302        3         
虚拟         3         
虚拟         3         
虚拟         3         
401        4         
虚拟         4         
虚拟         4         
虚拟         4         
虚拟         4         
*/--删除测试
drop table #room,#temp

Answer 4

use test
go
CREATE TABLE room (code char(10),floor char(10)) 
INSERT INTO room VALUES ( '201 ', '2 ') 
INSERT INTO room VALUES ( '202 ', '2 ') 
INSERT INTO room VALUES ( '203 ', '2 ') 
INSERT INTO room VALUES ( '301 ', '3 ') 
INSERT INTO room VALUES ( '302 ', '3 ') 
INSERT INTO room VALUES ( '401 ', '4 ') 
goselect [code]=isnull(t2.code,'虚拟'),[floor]=t1.col2
from 
(
select left(b.Code,2)+rtrim(t.col1) as col1,max([floor]) as col2
from (select 1 as col1 union select 2 union select 3 union select 4 union select 5)t
cross join room b group by left(b.Code,2)+rtrim(t.col1)
)t1
left join
room t2 on t1.col1=t2.code

Answer 5

谢谢楼上的各位高手，这里有一点要注意，就是
5 这个只是个变量。假设是 @a
不能写死select top @a identity(int,1,1) as id
into #q
from sysobjects这样通不过，请在帮忙一下，谢谢

Answer 6

create table room (code char(10),floor char(10)) 
insert into room values ( '201 ', '2 ') 
insert into room values ( '202 ', '2 ') 
insert into room values ( '203 ', '2 ') 
insert into room values ( '301 ', '3 ') 
insert into room values ( '302 ', '3 ') 
insert into room values ( '401 ', '4 ') 
declare @i int,@sql varchar(8000)set @i=5
set @sql ='
select left(code,1) as id
into #p
from room
group by left(code,1)
select top ' + cast(@i as varchar(10)) + ' identity(int,1,1) as id
into #q
from sysobjects
select cast(a.id as varchar(10)) + ''0'' + cast(b.id as varchar(10)) as id
into #r
from #p as a inner join #q as b on 1=1insert into room 
select ''虚拟'',left(id,1)
from #r as a left join room as b on a.id=b.code
where b.code is null
'exec (@sql)select * 
from room
order by floor,codedrop table room

Answer 7

改成用动态SQL语句。
动态sql语句基本语法 
1 :普通SQL语句可以用Exec执行 eg:   Select * from tableName 
         Exec('select * from tableName') 
         Exec sp_executesql N'select * from tableName'    -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg:   
declare @fname varchar(20) 
set @fname = 'FiledName' 
Select @fname from tableName              -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 
Exec('select ' + @fname + ' from tableName')     -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 
declare @fname varchar(20) 
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s)                -- 成功 
exec sp_executesql @s   -- 此句会报错 declare @s Nvarchar(1000)  -- 注意此处改为nvarchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s)                -- 成功     
exec sp_executesql @s   -- 此句正确 3. 输出参数 
declare @num int, @sql nvarchar(4000) 
set @sql='select count(*) from tableName' 
exec(@sql) 
--如何将exec执行结果放入变量中？ declare @num int, @sql nvarchar(4000) 
set @sql='select @a=count(*) from tableName ' 
exec sp_executesql @sql,N'@a int output',@num output 
select @num 

Answer 8

如果不用动态SQL，可以参考二楼的方法。

Answer 9

看错，二楼也用了动态SQL。

Answer 10

use test
go
CREATE TABLE room (code char(10),floor char(10)) 
INSERT INTO room VALUES ( '201 ', '2 ') 
INSERT INTO room VALUES ( '202 ', '2 ') 
INSERT INTO room VALUES ( '203 ', '2 ') 
INSERT INTO room VALUES ( '301 ', '3 ') 
INSERT INTO room VALUES ( '302 ', '3 ') 
INSERT INTO room VALUES ( '401 ', '4 ') 
goselect ID=right(t1.col1,1),Room=t1.col1,[code]=isnull(t2.code,'虚拟'),[floor]=t1.col2
into #t--生成临时表
from 
(
select left(b.Code,2)+rtrim(t.col1) as col1,max([floor]) as col2
from (select 1 as col1 union select 2 union select 3 union select 4 union select 5)t
cross join room b group by left(b.Code,2)+rtrim(t.col1)
)t1
left join
room t2 on t1.col1=t2.codego
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'code'+rtrim(ID)+'=max( case when right(room,1)='+rtrim(ID)+' then code else '''' end)'
from #t 
group by ID
--print @s
exec( 'select '+@s+' from #t group by left(room,2)')go
code1      code2      code3      code4      code5      
---------- ---------- ---------- ---------- ---------- 
201        202        203        虚拟         虚拟      
301        302        虚拟         虚拟         虚拟      
401        虚拟         虚拟         虚拟         虚拟      

Answer 11

把以上语句放在存储过程里，把go去掉:
create proc test_p
as
select ID=right(t1.col1,1),Room=t1.col1,[code]=isnull(t2.code,'虚拟'),[floor]=t1.col2
into #t--生成临时表
from 
(
select left(b.Code,2)+rtrim(t.col1) as col1,max([floor]) as col2
from (select 1 as col1 union select 2 union select 3 union select 4 union select 5)t
cross join room b group by left(b.Code,2)+rtrim(t.col1)
)t1
left join
room t2 on t1.col1=t2.codedeclare @s nvarchar(4000)
select @s=isnull(@s+',','')+'code'+rtrim(ID)+'=max( case when right(room,1)='+rtrim(ID)+' then code else '''' end)'
from #t 
group by ID
--print @s
exec( 'select '+@s+' from #t group by left(room,2)')go
exec test_p
（所影响的行数为 15 行）code1      code2      code3      code4      code5      
---------- ---------- ---------- ---------- ---------- 
201        202        203        虚拟         虚拟      
301        302        虚拟         虚拟         虚拟      
401        虚拟         虚拟         虚拟         虚拟      （所影响的行数为 3 行）

Answer 12

CSDN总是自动加空格，新版一直有这个Bug

Answer 13

楼上的大哥。你误会我的意思了。我要的结果是/*
code       floor      
---------- ---------- 
201        2         
202        2         
203        2         
虚拟         2         
虚拟         2         
301        3         
302        3         
虚拟         3         
虚拟         3         
虚拟         3         
401        4         
虚拟         4         
虚拟         4         
虚拟         4         
虚拟         4         
*/
并不是
code1      code2      code3      code4      code5      
---------- ---------- ---------- ---------- ---------- 
201        202        203        虚拟         虚拟      
301        302        虚拟         虚拟         虚拟      
401        虚拟         虚拟         虚拟         虚拟      
哦
可以帮我吗？

Answer 14

我上面说过了，把select cast(a.id as varchar(10)) +  ' '0 ' ' + cast(b.id as varchar(10)) as id 这句中0两侧的单引号连在一起，如：''0''CSDN总是自动加空格，新版一直有这个Bug

Answer 15

use test
go
CREATE TABLE room (code char(10),floor char(10)) 
INSERT INTO room VALUES ( '201 ', '2 ') 
INSERT INTO room VALUES ( '202 ', '2 ') 
INSERT INTO room VALUES ( '203 ', '2 ') 
INSERT INTO room VALUES ( '301 ', '3 ') 
INSERT INTO room VALUES ( '302 ', '3 ') 
INSERT INTO room VALUES ( '401 ', '4 ') 
godrop proc test_p
go
create proc test_p(@i int)
as
--生成表变量
declare @j int
set @j=1
declare @t table(col1 int)
while @j!>@i
begin
insert @t select @j
set @j=@j+1
endselect ID=right(t1.col1,1),Room=t1.col1,[code]=isnull(t2.code,'虚拟'),[floor]=t1.col2
into #t--生成临时表
from 
(
select left(b.Code,2)+rtrim(t.col1) as col1,max([floor]) as col2
from @t t
cross join room b group by left(b.Code,2)+rtrim(t.col1)
)t1
left join
room t2 on t1.col1=t2.codedeclare @s nvarchar(4000)
select @s=isnull(@s+',','')+'code'+rtrim(ID)+'=max( case when right(room,1)='+rtrim(ID)+' then code else '''' end)'
from #t 
group by ID
--print @s
exec( 'select '+@s+' from #t group by left(room,2)')go
exec test_p 6
code1      code2      code3      code4      code5      code6      
---------- ---------- ---------- ---------- ---------- ---------- 
201        202        203        虚拟         虚拟         虚拟      
301        302        虚拟         虚拟         虚拟         虚拟      
401        虚拟         虚拟         虚拟         虚拟         虚拟      （所影响的行数为 3 行）

Answer 16

create table room (code char(10),floor char(10))  
insert into room values (  '201  ',  '2  ')  
insert into room values (  '202  ',  '2  ')  
insert into room values (  '203  ',  '2  ')  
insert into room values (  '301  ',  '3  ')  
insert into room values (  '302  ',  '3  ')  
insert into room values (  '401  ',  '4  ')  
declare @i int,@sql varchar(8000) set @i=5 
set @sql = ' 
select left(code,1) as id 
into #p 
from room 
group by left(code,1) 
select top  ' + cast(@i as varchar(10)) +  ' identity(int,1,1) as id 
into #q 
from sysobjects 
select cast(a.id as varchar(10)) +  ''0 '' + cast(b.id as varchar(10)) as id 
into #r 
from #p as a inner join #q as b on 1=1 insert into room  
select  ''虚拟 '',left(id,1) 
from #r as a left join room as b on a.id=b.code 
where b.code is null 
' exec (@sql) select *  
from room 
order by floor,code drop table room 

Answer 17

to  不知不为过，不学就是错！
------------------------------------create table room (code char(10),floor char(10))  
insert into room values (  '201  ',  '2  ')  
insert into room values (  '202  ',  '2  ')  
insert into room values (  '203  ',  '2  ')  
insert into room values (  '301  ',  '3  ')  
insert into room values (  '302  ',  '3  ')  
insert into room values (  '401  ',  '4  ')  
declare @i int,@sql varchar(8000) set @i=5 
set @sql = ' 
select left(code,1) as id 
into #p 
from room 
group by left(code,1) 
select top  ' + cast(@i as varchar(10)) +  ' identity(int,1,1) as id 
into #q 
from sysobjects 
select cast(a.id as varchar(10)) +  ''0 '' + cast(b.id as varchar(10)) as id 
into #r 
from #p as a inner join #q as b on 1=1 insert into room  
select  ''虚拟 '',left(id,1) 
from #r as a left join room as b on a.id=b.code 
where b.code is null 
' exec (@sql) select *  
from room 
order by floor,code drop table room 
执行结果不符合我要求哦。

Answer 18

晕，又被CSDN自动加空格了。

Answer 19

''0 '' -->把0后面的空格去掉，CSDN总是自动加空格''0''

Answer 20

执行结果不符合是因为0后面被CSDN自动加了个空格，你删掉就可以了。

Answer 21

--创建测试
CREATE TABLE #room (code char(10),floor char(10))
INSERT INTO #room VALUES ('201','2')
INSERT INTO #room VALUES ('202','2')
INSERT INTO #room VALUES ('203','2')
INSERT INTO #room VALUES ('301','3')
INSERT INTO #room VALUES ('302','3')
INSERT INTO #room VALUES ('401','4')--每行可画N个房间
declare @N int
set @N=6create table #temp (id int identity(1,1),none bit)exec ('insert #temp select top '+@N+' 0 from syscolumns a,syscolumns b')insert #room select '虚拟',a.floor
    from
        (select distinct floor from #room) a
            cross join
        #temp b
            left join
    (select *,id=(select count(1) from #room where floor=a.floor and code<=a.code) from #room a) c
        on a.floor=c.floor and b.id=c.id
where c.code is nullselect * from #room order by floor,code
/*
code       floor      
---------- ---------- 
201        2         
202        2         
203        2         
虚拟         2         
虚拟         2         
虚拟         2         
301        3         
302        3         
虚拟         3         
虚拟         3         
虚拟         3         
虚拟         3         
401        4         
虚拟         4         
虚拟         4         
虚拟         4         
虚拟         4         
虚拟         4         
*/--删除测试
drop table #room,#temp

Answer 22

生成格式:
use test
go
CREATE TABLE room (code char(10),floor char(10)) 
INSERT INTO room VALUES ( '201 ', '2 ') 
INSERT INTO room VALUES ( '202 ', '2 ') 
INSERT INTO room VALUES ( '203 ', '2 ') 
INSERT INTO room VALUES ( '301 ', '3 ') 
INSERT INTO room VALUES ( '302 ', '3 ') 
INSERT INTO room VALUES ( '401 ', '4 ') 
godrop proc test_p
go
create proc test_p(@i int)
as
--生成表变量
declare @j int
set @j=1
declare @t table(col1 int)
while @j!>@i
begin
    insert @t select @j
    set @j=@j+1
endselect [code]=isnull(t2.code,'虚拟'),[floor]=t1.col2from 
(
select left(b.Code,2)+rtrim(t.col1) as col1,max([floor]) as col2
from @t t
cross join    room b group by left(b.Code,2)+rtrim(t.col1)
)t1
left join
room t2 on t1.col1=t2.code
goexec test_p 6gocode       floor      
---------- ---------- 
201        2         
202        2         
203        2         
虚拟         2         
虚拟         2         
虚拟         2         
301        3         
302        3         
虚拟         3         
虚拟         3         
虚拟         3         
虚拟         3         
401        4         
虚拟         4         
虚拟         4         
虚拟         4         
虚拟         4         
虚拟         4         （所影响的行数为 18 行）

Answer 23

--最后再贴一次，CSDN不要再自动加空格了create table room (code char(10),floor char(10))   
insert into room values (   '201   ',   '2   ')   
insert into room values (   '202   ',   '2   ')   
insert into room values (   '203   ',   '2   ')   
insert into room values (   '301   ',   '3   ')   
insert into room values (   '302   ',   '3   ')   
insert into room values (   '401   ',   '4   ')   
declare @i int,@sql varchar(8000)  set @i=5  
set @sql =  '  
select left(code,1) as id  
into #p  
from room  
group by left(code,1)  
select top   ' + cast(@i as varchar(10)) +   ' identity(int,1,1) as id  
into #q  
from sysobjects  
select cast(a.id as varchar(10)) +   ''0'' + cast(b.id as varchar(10)) as id  
into #r  
from #p as a inner join #q as b on 1=1  insert into room   
select   ''虚拟'',left(id,1)  
from #r as a left join room as b on a.id=b.code  
where b.code is null  
'  exec (@sql)  select *   
from room  
order by floor,code  drop table room  

Answer 24

to： 中国风_燃烧你的激情！！！非常非常感谢， 已经ok了to:不知不为过，不学就是错！
也非常非常感谢， 也已经ok了受益良多，真诚感谢。
慢慢消化，明天结贴。

Answer 25

不好意思，各位高手们，问题我描述没有完整，上面给出的只是一个特例
现在描述完整。请求再次帮忙有一个房间表。 
CREATE TABLE room (code char(10),floor char(10)) 
INSERT INTO room VALUES ( '201 ', '2 ') 
INSERT INTO room VALUES ( '202 ', '2 ') 
INSERT INTO room VALUES ( '203 ', '2 ') 
INSERT INTO room VALUES ( '301 ', '3 ') 
INSERT INTO room VALUES ( '302 ', '3 ') 
INSERT INTO room VALUES ( '401 ', '4 ') 
要根据以上数据画房间的列表图，已知每行可画@n个房间 
这里@n 不是固定的，有可能比每层的房间数量多，也可能
比每层的房间数量少，举例而言，假设@n = 5 那
希望能用sql 或者存储过程实现下面的数据 
'201 ', '2 ' 
'202 ', '2 ' 
'203 ', '2 ' 
'虚拟 ', '2 ' 
'虚拟 ', '2 ' 
'301 ', '3 ' 
'302 ', '3 ' 
'虚拟 ', '3 ' 
'虚拟 ', '3 ' 
'虚拟 ', '3 ' 
'401 ', '4 ' 
'虚拟 ', '4 ' 
'虚拟 ', '4 ' 
'虚拟 ', '4 ' 
'虚拟 ', '4 ' 
假设 @n =2 的话 ，那么
希望能用sql 或者存储过程实现下面的数据 
'201 ', '2 ' 
'202 ', '2 ' 
'203 ', '2 ' 
'虚拟 ', '2 ' 
'301 ', '3 ' 
'302 ', '3 ' 
'401 ', '4 ' 
'虚拟 ', '4 ' 
--注意 @n 只是变量，并不只是，2或者5
如果有了上述的数据，我就可以要达到下面的图形效果 假设@n = 5
201  202    203     '虚拟 '   '虚拟 ' 
301  302   '虚拟 '  '虚拟 '   '虚拟 ' 
401 '虚拟' '虚拟 '  '虚拟 '   '虚拟 ' 假设@n = 2
201  202  
203 '虚拟 ' 
301  302 
401 '虚拟'--上面的图形只是意思而已，关键是需要数据 
 请教如何用sql 语法或者存储过程来实现这样的结果？谢谢大家 

Answer 26

/*将room查询结果送到room1中*/
select *  into  room1 from room
/*room 和 room1进行交叉连接
select *
from room a
cross join room1 b
然后在提取数据