IF EXISTS(SELECT name FROM sys.objects WHERE name = 'test') DROP TABLE test go CREATE TABLE test ( tt CHAR(4) ) GOINSERT INTO test (tt) SELECT '0100' UNION ALL SELECT '0300' UNION ALL SELECT '0800' UNION ALL SELECT '0200' UNION ALL SELECT '0500' --另外有10个变量@t1 , @t2 , @t3 , @t4 , @t5 ,@t6 , @t7 , @t8 , @t9 , @t10 --想要将test表中的数据按照从小到大的顺序放入到这10个变量中 --test表中的笔数不定但不会超过10笔,该如何处理比较方便,不想使用游标 DECLARE @t1 CHAR(4),@t2 CHAR(4),@t3 CHAR(4),@t4 CHAR(4), @t5 CHAR(4),@t6 CHAR(4),@t7 CHAR(4),@t8 CHAR(4), @t9 CHAR(4),@t10 CHAR(4) SELECT @t1=tt FROM (SELECT id=ROW_NUMBER()OVER(ORDER BY tt),* FROM test)AS t WHERE id=1SELECT @t1 /*---- 0100(1 行受影响)*/
谢谢版主给的思路 IF EXISTS(SELECT name FROM sys.objects WHERE name = 'test') DROP TABLE test go CREATE TABLE test ( tt CHAR(4) ) GOINSERT INTO test (tt) SELECT '0100' UNION ALL SELECT '0300' UNION ALL SELECT '0800' UNION ALL SELECT '0200' UNION ALL SELECT '0500' --另外有10个变量@t1 , @t2 , @t3 , @t4 , @t5 ,@t6 , @t7 , @t8 , @t9 , @t10 --想要将test表中的数据按照从小到大的顺序放入到这10个变量中 --test表中的笔数不定但不会超过10笔,该如何处理比较方便,不想使用游标 DECLARE @t1 CHAR(04) , @t2 CHAR(04) , @t3 CHAR(04) ,@t4 CHAR(04), @t5 CHAR(04) , @t6 CHAR(04) , @t7 CHAR(04) WITH a AS ( SELECT ROW_NUMBER() OVER(ORDER BY tt) AS rown , tt FROM dbo.test )SELECT @t1 = MAX(CASE rown WHEN 1 THEN tt ELSE '' END), @t2 = MAX(CASE rown WHEN 2 THEN tt ELSE '' END), @t3 = MAX(CASE rown WHEN 3 THEN tt ELSE '' END), @t4 = MAX(CASE rown WHEN 4 THEN tt ELSE '' END), @t5 = MAX(CASE rown WHEN 5 THEN tt ELSE '' END), @t6 = MAX(CASE rown WHEN 6 THEN tt ELSE '' end), @t7 = MAX(CASE rown WHEN 7 THEN tt ELSE '' END) FROM aPRINT @t1 PRINT @t2 PRINT @t3 PRINT @t4 PRINT @t5 PRINT @t6 PRINT @t7/*---------结果------ 0100 0200 0300 0500 0800 */
DROP TABLE test
go
CREATE TABLE test
(
tt CHAR(4)
)
GOINSERT INTO test (tt)
SELECT '0100' UNION ALL
SELECT '0300' UNION ALL
SELECT '0800' UNION ALL
SELECT '0200' UNION ALL
SELECT '0500' --另外有10个变量@t1 , @t2 , @t3 , @t4 , @t5 ,@t6 , @t7 , @t8 , @t9 , @t10
--想要将test表中的数据按照从小到大的顺序放入到这10个变量中
--test表中的笔数不定但不会超过10笔,该如何处理比较方便,不想使用游标
DECLARE @t1 CHAR(4),@t2 CHAR(4),@t3 CHAR(4),@t4 CHAR(4), @t5 CHAR(4),@t6 CHAR(4),@t7 CHAR(4),@t8 CHAR(4), @t9 CHAR(4),@t10 CHAR(4)
SELECT @t1=tt FROM (SELECT id=ROW_NUMBER()OVER(ORDER BY tt),* FROM test)AS t WHERE id=1SELECT @t1
/*----
0100(1 行受影响)*/
IF EXISTS(SELECT name FROM sys.objects WHERE name = 'test')
DROP TABLE test
go
CREATE TABLE test
(
tt CHAR(4)
)
GOINSERT INTO test (tt)
SELECT '0100' UNION ALL
SELECT '0300' UNION ALL
SELECT '0800' UNION ALL
SELECT '0200' UNION ALL
SELECT '0500' --另外有10个变量@t1 , @t2 , @t3 , @t4 , @t5 ,@t6 , @t7 , @t8 , @t9 , @t10
--想要将test表中的数据按照从小到大的顺序放入到这10个变量中
--test表中的笔数不定但不会超过10笔,该如何处理比较方便,不想使用游标
DECLARE @t1 CHAR(04) , @t2 CHAR(04) , @t3 CHAR(04) ,@t4 CHAR(04), @t5 CHAR(04) , @t6 CHAR(04) , @t7 CHAR(04)
WITH a AS (
SELECT ROW_NUMBER() OVER(ORDER BY tt) AS rown , tt FROM dbo.test
)SELECT @t1 = MAX(CASE rown WHEN 1 THEN tt ELSE '' END),
@t2 = MAX(CASE rown WHEN 2 THEN tt ELSE '' END),
@t3 = MAX(CASE rown WHEN 3 THEN tt ELSE '' END),
@t4 = MAX(CASE rown WHEN 4 THEN tt ELSE '' END),
@t5 = MAX(CASE rown WHEN 5 THEN tt ELSE '' END),
@t6 = MAX(CASE rown WHEN 6 THEN tt ELSE '' end),
@t7 = MAX(CASE rown WHEN 7 THEN tt ELSE '' END)
FROM aPRINT @t1
PRINT @t2
PRINT @t3
PRINT @t4
PRINT @t5
PRINT @t6
PRINT @t7/*---------结果------
0100
0200
0300
0500
0800
*/