猜数字

最新推荐文章于 2024-09-16 13:12:23 发布

cuiyongluo0054

最新推荐文章于 2024-09-16 13:12:23 发布

阅读量131

点赞数

在优快云上看到一个很有意思的问题：

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

(

)

--把数据追加到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