SqlServer中的排序问题

[嗯哼a]

我有一个列，里面是4，5,6,7,8,9,10,11,12,1,2,3,0，-1，-2，-3，-4
如何查询显示的时候排列为4,5,6，-1,7,8,9，-2,10,11,12，-3,1,2,3-4,0这种样子

[嗯哼a]

求大神呀。。。。

[卖水果的net]

没看出什么规律来？ 还是说就按这几个数值写死？

[学海无涯-回头是岸]

有规律吗？ 前面一个负数跟3个正数，然后负数-1前面的max（正数+1）三个 这种吗？

[Tiger_Zhao]

/* 测试数据
WITH t(col1,col2) AS (
    SELECT 4,'A' UNION ALL
    SELECT 5,'B' UNION ALL
    SELECT 6,'C' UNION ALL
    SELECT 7,'D' UNION ALL
    SELECT 8,'E' UNION ALL
    SELECT 9,'F' UNION ALL
    SELECT 10,'G' UNION ALL
    SELECT 11,'H' UNION ALL
    SELECT 12,'I' UNION ALL
    SELECT 1,'J' UNION ALL
    SELECT 2,'K' UNION ALL
    SELECT 3,'L' UNION ALL
    SELECT 0,'M' UNION ALL
    SELECT -1,'N' UNION ALL
    SELECT -2,'O' UNION ALL
    SELECT -3,'P' UNION ALL
    SELECT -4,'Q'
)
SELECT * INTO table1 FROM t
 */
 
-- 查询
;WITH a(col1,sort) AS (
    SELECT 4,1 UNION ALL
    SELECT 5,2 UNION ALL
    SELECT 6,3 UNION ALL
    SELECT -1,4 UNION ALL
    SELECT 7,5 UNION ALL
    SELECT 8,6 UNION ALL
    SELECT 9,7 UNION ALL
    SELECT -2,8 UNION ALL
    SELECT 10,9 UNION ALL
    SELECT 11,10 UNION ALL
    SELECT 12,11 UNION ALL
    SELECT -3,12 UNION ALL
    SELECT 1,13 UNION ALL
    SELECT 2,14 UNION ALL
    SELECT 3,15 UNION ALL
    SELECT -4,16 UNION ALL
    SELECT 0,17
)
    SELECT table1.*
      FROM table1
      JOIN a
        ON table1.col1 = a.col1
  ORDER BY a.sort

       col1 col2
----------- ----
          4 A
          5 B
          6 C
         -1 N
          7 D
          8 E
          9 F
         -2 O
         10 G
         11 H
         12 I
         -3 P
          1 J
          2 K
          3 L
         -4 Q
          0 M

保证正确

[卖水果的net]

引用 4 楼 Tiger_Zhao 的回复:

保证正确

[学海无涯-回头是岸]

引用 4 楼 Tiger_Zhao 的回复:

/* 测试数据
WITH t(col1,col2) AS (
    SELECT 4,'A' UNION ALL
    SELECT 5,'B' UNION ALL
    SELECT 6,'C' UNION ALL
    SELECT 7,'D' UNION ALL
    SELECT 8,'E' UNION ALL
    SELECT 9,'F' UNION ALL
    SELECT 10,'G' UNION ALL
    SELECT 11,'H' UNION ALL
    SELECT 12,'I' UNION ALL
    SELECT 1,'J' UNION ALL
    SELECT 2,'K' UNION ALL
    SELECT 3,'L' UNION ALL
    SELECT 0,'M' UNION ALL
    SELECT -1,'N' UNION ALL
    SELECT -2,'O' UNION ALL
    SELECT -3,'P' UNION ALL
    SELECT -4,'Q'
)
SELECT * INTO table1 FROM t
 */
 
-- 查询
;WITH a(col1,sort) AS (
    SELECT 4,1 UNION ALL
    SELECT 5,2 UNION ALL
    SELECT 6,3 UNION ALL
    SELECT -1,4 UNION ALL
    SELECT 7,5 UNION ALL
    SELECT 8,6 UNION ALL
    SELECT 9,7 UNION ALL
    SELECT -2,8 UNION ALL
    SELECT 10,9 UNION ALL
    SELECT 11,10 UNION ALL
    SELECT 12,11 UNION ALL
    SELECT -3,12 UNION ALL
    SELECT 1,13 UNION ALL
    SELECT 2,14 UNION ALL
    SELECT 3,15 UNION ALL
    SELECT -4,16 UNION ALL
    SELECT 0,17
)
    SELECT table1.*
      FROM table1
      JOIN a
        ON table1.col1 = a.col1
  ORDER BY a.sort

       col1 col2
----------- ----
          4 A
          5 B
          6 C
         -1 N
          7 D
          8 E
          9 F
         -2 O
         10 G
         11 H
         12 I
         -3 P
          1 J
          2 K
          3 L
         -4 Q
          0 M

保证正确

你这 。。。高啊 实在是高

[中国风]

根据条件显示顺序 e.g.

DECLARE @str VARCHAR(max)='4,5,6,-1,7,8,9,-2,10,11,12,-3,1,2,3-4,0'
SELECT * FROM table1 WHERE CHARINDEX(','+RTRIM(ID)+',',@str)>0  ORDER BY CHARINDEX(','+RTRIM(ID)+',',','+@str+',') 

[中国风]

如：同一个表没条件要改变表的顺序，直接加多1列用于排序 如果顺序让程序传照方法：#7