好奇就试了试。
表1 matrix
orderlist | value | value2 | value3 |
---|---|---|---|
a11 | 1 | ||
a12 | 2 | ||
a13 | 3 | ||
a21 | 4 | ||
a22 | 5 | ||
a23 | 6 | ||
a31 | 7 | ||
a32 | 8 | ||
a33 | 9 |
代码
DECLARE @i INT,@j INT
DECLARE @a nvarchar(50) =1
DECLARE @value NVARCHAR(50)=0
DECLARE @valuesum NVARCHAR(50)
SET @i=1
WHILE @i<=3
BEGIN
SET @j=1
SET @valuesum=''
WHILE @j<=3
BEGIN
SET @a=CONVERT(NVARCHAR(1),@i)+CONVERT(NVARCHAR(1),@j)
SET @value=(SELECT VALUE FROM matrix WHERE orderlist='a'+@a)
SET @valuesum+=@value+' '
SET @j+=1
END
PRINT(@valuesum)
PRINT(CHAR(13))
SET @i+=1
END
结果
另外我又想到了矩阵数据更新,所以这里我建了3列value,就可以保留上1次的数据(其中一列设null值用来判断更新数据,null值循环)
--首先不能用function,好像有update就不能用
CREATE proc autosave
@num nvarchar(50),
@value nvarchar(50)
AS
begin
--通过判断matrix的value,value2,value3的值来更新和保留数据
--这里只用value有数据,value2和value3为null举例
IF EXISTS(SELECT VALUE FROM matrix WHERE orderlist=@num )
--这里不能用not exists,因为null也被视为存在,也不能用=null
AND (SELECT VALUE2 FROM matrix WHERE orderlist=@num )IS null
AND (SELECT VALUE3 FROM matrix WHERE orderlist=@num )is null
BEGIN
--更新新数据
UPDATE matrix SET value2=@value--value3=null --删除旧数据
WHERE orderlist=@num
end
END
附带1个其他的方法(5*5)
/*利用系统的数字辅助表,生成1-25及每连续5个数一组的组号(从1开始)*/
WITH x0
AS ( SELECT ( number - 1 ) / 5 + 1 AS cn ,
number AS seq
FROM master..spt_values
WHERE number <= 25
AND number >= 1
AND type = 'P'
),/*新增一列,按组内降序排序,及在同组内从大到小排序*/
x1
AS ( SELECT TOP 25
cn ,
seq ,
ROW_NUMBER() OVER ( ORDER BY cn, seq DESC ) dseq
FROM x0
ORDER BY cn ,
seq
)
/*如果是单行号,则升序;否则,降序*/
SELECT MAX(CASE seq % 5
WHEN 1 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS A ,
MAX(CASE seq % 5
WHEN 2 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS B ,
MAX(CASE seq % 5
WHEN 3 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS C ,
MAX(CASE seq % 5
WHEN 4 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS D ,
MAX(CASE seq % 5
WHEN 0 THEN CASE cn % 2
WHEN 1 THEN seq
ELSE dseq
END
END) AS E
FROM x1
GROUP BY cn
结果