猜数字

在CSDN上看到一个很有意思的问题：

create TABLE [dbo].[t_caishu](

ID INT IDENTITY(1,1) NOT  NULL ,
c1] [int] NOT NULL CONSTRAINT [DF_t_caishu_c1]  DEFAULT ((0)),
[c2] [int] NOT NULL CONSTRAINT [DF_t_caishu_c2]  DEFAULT ((0)),
[c3] [int] NOT NULL CONSTRAINT [DF_t_caishu_c3]  DEFAULT ((0)),
[c4] [int] NOT NULL CONSTRAINT [DF_t_caishu_c4]  DEFAULT ((0)),
[c5] [int] NOT NULL CONSTRAINT [DF_t_caishu_c5]  DEFAULT ((0)),
[c6] [int] NOT NULL CONSTRAINT [DF_t_caishu_c6]  DEFAULT ((0)),
[c7] [int] NOT NULL CONSTRAINT [DF_t_caishu_c7]  DEFAULT ((0)),
[c8] [int] NOT NULL CONSTRAINT [DF_t_caishu_c8]  DEFAULT ((0)),
[c9] [int] NOT NULL CONSTRAINT [DF_t_caishu_c9]  DEFAULT ((0))
) ON [PRIMARY]

go
insert into [t_caishu](c1,c3,c4,c8)values(3,5,6,8)
insert into [t_caishu](c3,c7)values(8,6)
insert into [t_caishu](c2,c6,c8)values(7,1,5)

insert into [t_caishu](c1,c2,c5)values(9,6,2)
insert into [t_caishu](c4,c6)values(9,5)
insert into [t_caishu](c5,c8,c9)values(4,3,9)

insert into [t_caishu](c2,c4,c8)values(4,5,7)
insert into [t_caishu](c3,c7)values(7,2)
insert into [t_caishu](c2,c6,c7,c9)values(1,7,3,4)
go
select * from  [t_caishu]

就这样一个9行9列的表，最后要得到一个2列的表，数据为

'c21','2,9'
'c51','1,7,9'
...
'c12','1,2,4,5,7'
...

我的解决方法是：

--首先创建一个带自动标识列的表TT

CREATE TABLE TT

(

ID INT IDENTITY(1,1) NOT  NULL ,
c1] [int] NOT NULL CONSTRAINT [DF_t_caishu_c1]  DEFAULT ((0)),
[c2] [int] NOT NULL CONSTRAINT [DF_t_caishu_c2]  DEFAULT ((0)),
[c3] [int] NOT NULL CONSTRAINT [DF_t_caishu_c3]  DEFAULT ((0)),
[c4] [int] NOT NULL CONSTRAINT [DF_t_caishu_c4]  DEFAULT ((0)),
[c5] [int] NOT NULL CONSTRAINT [DF_t_caishu_c5]  DEFAULT ((0)),
[c6] [int] NOT NULL CONSTRAINT [DF_t_caishu_c6]  DEFAULT ((0)),
[c7] [int] NOT NULL CONSTRAINT [DF_t_caishu_c7]  DEFAULT ((0)),
[c8] [int] NOT NULL CONSTRAINT [DF_t_caishu_c8]  DEFAULT ((0)),
[c9] [int] NOT NULL CONSTRAINT [DF_t_caishu_c9]  DEFAULT ((0))

)

--把数据追加到TT表中

INSERT INTO TT (C1,C2,C3,C4,C5,C6,C7,C8,C9)
SELECT C1,C2,C3,C4,C5,C6,C7,C8,C9
FROM t_caishu

--然后循环读取行列数据进行数据判断

DECLARE @Row INT,
  @Col INT,    
  @RowChar NVARCHAR(100),
  @ColChar NvARCHAR(100),
  @ColName NVARCHAR(10),
  @ColNum  NVARCHAR(100),
  @NvarDes NVARCHAR(100),
  @I   INT,
  @SQL  NVARCHAR(100)

SET @Row=1
WHILE @Row<=9 --行循环
BEGIN
 --得到行数据
 SET @RowChar=''
 SELECT @RowChar= @RowChar + F.Num + ',' FROM
 (
  SELECT CONVERT(NCHAR(1),C1) AS Num FROM TT WHERE ID=@Row UNION ALL
  SELECT CONVERT(NCHAR(1),C2) AS Num FROM TT WHERE ID=@Row UNION ALL
  SELECT CONVERT(NCHAR(1),C3) AS Num FROM TT WHERE ID=@Row UNION ALL
  SELECT CONVERT(NCHAR(1),C4) AS Num FROM TT WHERE ID=@Row UNION ALL
  SELECT CONVERT(NCHAR(1),C5) AS Num FROM TT WHERE ID=@Row UNION ALL
  SELECT CONVERT(NCHAR(1),C6) AS Num FROM TT WHERE ID=@Row UNION ALL
  SELECT CONVERT(NCHAR(1),C7) AS Num FROM TT WHERE ID=@Row UNION ALL
  SELECT CONVERT(NCHAR(1),C8) AS Num FROM TT WHERE ID=@Row UNION ALL
  SELECT CONVERT(NCHAR(1),C9) AS Num FROM TT WHERE ID=@Row
 ) F
 
 SET @Col=1
 SET @SQL=''
 WHILE @Col<=9  --列循环
 BEGIN
  --得到列数据
  SET @ColChar=''
  SET @SQL ='SELECT @S =@S + CONVERT(CHAR(1),C'+CONVERT(NCHAR(1),@Col)+') +'','' FROM TT'
  EXEC sp_executesql @SQL,N'@S NVARCHAR(100) OUTPUT',@ColChar OUTPUT
  --构造行列字符串
  SET @NvarDes=''
  SET @NvarDes=@RowChar+LEFT(@ColChar,LEN(@Colchar)-1)
  
  --查找未出现的数字
  SET @ColName='C'+CONVERT(NCHAR(1),@Col)+CONVERT(NCHAR(1),@Row)
  SET @ColNum=''
  SET @I=1
  WHILE @I<=9
  BEGIN
   IF CHARINDEX(CONVERT(NCHAR(1),@I),@NvarDes)=0
   BEGIN
    SET @ColNum=@ColNum+CONVERT(NCHAR(1),@I)+','
   END
   SET @I=@I+1
  END
  --得到最终数据
  PRINT @ColName+'    '+ LEFT(@ColNum,LEN(@ColNum)-1)
  
  --循环下一列
  SET @Col=@Col+1
 END
--循环下一行
 SET @Row=@Row+1
END

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15042150/viewspace-496146/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15042150/viewspace-496146/