分组轮回

[nickxzh]

有 P1到P12，分成两组，P1到P6分为A组，P7到P12分为B组。
第一次的时候，选出A组的P1 和B组所有；第二次的时候，选出A组的P2和B组所有；..... 直到到六次，选出A组的P6和B组所有；
然后第7次的时候，选出B组的P7和A组所有；第8次的时候，选出B组的P8和A组所有；......第13次的时候，又从头开始，选出的是A组的P1和B组所有。
每次查询的结果会有保存到一个表里面。
这样的代码怎么写？

[二月十六]

建议楼主列出表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382

1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样，（并给以简单的算法描述）
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)

这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[nickxzh]

引用 楼主 nickxzh 的回复:

有 P1到P12，分成两组，P1到P6分为A组，P7到P12分为B组。
第一次的时候，选出A组的P1 和B组所有；第二次的时候，选出A组的P2和B组所有；..... 直到到六次，选出A组的P6和B组所有；
然后第7次的时候，选出B组的P7和A组所有；第8次的时候，选出B组的P8和A组所有；......第13次的时候，又从头开始，选出的是A组的P1和B组所有。
每次查询的结果会有保存到一个表里面。
这样的代码怎么写？

补充：MS SQL数据库。
CREATE TABLE TP1( GroupCode NVARCHAR(5), PCode NVARCHAR(5))

INSERT INTO TP1
SELECT 'A' , 'P1'
UNION ALL
SELECT 'A' , 'P2'
UNION ALL
SELECT 'A' , 'P3'
UNION ALL
SELECT 'A' , 'P4'
UNION ALL
SELECT 'A' , 'P5'
UNION ALL
SELECT 'A' , 'P6'
UNION ALL
SELECT 'A' , 'P7'
UNION ALL
SELECT 'A' , 'P8'
UNION ALL
SELECT 'A' , 'P9'
UNION ALL
SELECT 'A' , 'P10'
UNION ALL
SELECT 'A' , 'P11'
UNION ALL
SELECT 'A' , 'P12'

/*
SELECT * FROM TP1
*/

结果表
CREATE TABLE TP2( Id INT , GroupCode NVARCHAR(5) , PCode NVARCHAR(5) , QDateTime DATETIME )

[nickxzh]

补充：
/*
第一次查询，结果为：
*/
INSERT INTO TP2
SELECT 1 AS Id, 'A' AS GroupCode , 'P1' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 1 AS Id, 'B' AS GroupCode , 'P7' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 1 AS Id, 'B' AS GroupCode , 'P8' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 1 AS Id, 'B' AS GroupCode , 'P9' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 1 AS Id, 'B' AS GroupCode , 'P10' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 1 AS Id, 'B' AS GroupCode , 'P11' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 1 AS Id, 'B' AS GroupCode , 'P12' AS PCode , GETDATE() AS QDateTime

[nickxzh]

补充：
/*
第二次查询，结果为：
*/
INSERT INTO TP2
SELECT 2 AS Id, 'A' AS GroupCode , 'P2' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 2 AS Id, 'B' AS GroupCode , 'P7' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 2 AS Id, 'B' AS GroupCode , 'P8' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 2 AS Id, 'B' AS GroupCode , 'P9' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 2 AS Id, 'B' AS GroupCode , 'P10' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 2 AS Id, 'B' AS GroupCode , 'P11' AS PCode , GETDATE() AS QDateTime
UNION ALL
SELECT 2 AS Id, 'B' AS GroupCode , 'P12' AS PCode , GETDATE() AS QDateTime

[RINK_1]

declare @i int --循环次数
declare @m int

set @i=28
set @m=1

while @m<=@i
begin 

if @m%12<=6 and @m%12<>0
begin
insert into #TP2
select @m,GroupCode,PCode,GETDATE() from #TP1 where CAST(REPLACE(PCode,'P','') AS INT)=@m%12
union all
select @m,GroupCode,PCode,GETDATE() from #TP1 where CAST(REPLACE(PCode,'P','') AS INT)>6
end

if @m%12>6 or @m%12=0
begin
insert into #TP2
select @m,GroupCode,PCode,GETDATE() from #TP1 where CAST(REPLACE(PCode,'P','') AS INT)=@m%12 or (@m%12=0 and CAST(REPLACE(PCode,'P','') AS INT)=12)
union all
select @m,GroupCode,PCode,GETDATE() from #TP1 where CAST(REPLACE(PCode,'P','') AS INT)<=6
end

set @m=@m+1

end

[二月十六]

DECLARE @num INT =2 --第几次循环
SELECT  *
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY CONVERT(INT, REPLACE(PCode,
                                                              'P', '')) ) rn
          FROM      TP1
        ) t
WHERE   rn = @num
UNION ALL
SELECT  *
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY CONVERT(INT, REPLACE(PCode,
                                                              'P', '')) ) rn
          FROM      TP1
        ) t
WHERE   rn BETWEEN CASE WHEN @num > 6 THEN 0
                        ELSE 6
                   END
           AND     CASE WHEN @num > 6 THEN 6
                        ELSE 12
                   END;