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 PROCEDURE SP_TEST(@N INT)
AS
BEGIN
    IF @N<1 
        return
    
    DECLARE @T TABLE(ID INT IDENTITY(1,1),CODE VARCHAR(4))
    
    SET ROWCOUNT 100
    INSERT INTO @T SELECT '' FROM syscolumns a,syscolumns b
    SET ROWCOUNT 0
    
    SELECT * FROM room
    UNION ALL
    SELECT
        '虚拟' as CODE,floor
    FROM
        (SELECT floor,count(*) as NUM FROM room group by floor) a,
        @T b
    WHERE
        (case when a.NUM%@N=0 then 0 else @N-a.NUM%@N end)>=b.ID
    ORDER BY 
        floor,code
END
GO

EXEC SP_TEST 2
/*
code       floor      
---------- ---------- 
201        2         
202        2         
203        2         
虚拟         2         
301        3         
302        3         
401        4         
虚拟         4 
*/

EXEC SP_TEST 5
/*
code       floor      
---------- ---------- 
201        2         
202        2         
203        2         
虚拟         2         
虚拟         2         
301        3         
302        3         
虚拟         3         
虚拟         3         
虚拟         3         
401        4         
虚拟         4         
虚拟         4         
虚拟         4         
虚拟         4 
*/

GO

DROP PROCEDURE SP_TEST
DROP TABLE room
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')
GO

CREATE PROCEDURE SP_TEST(@N INT)
AS
BEGIN
   IF @N<1  
       return
  
   DECLARE @T TABLE(ID INT I