按行排序

Question

我有一个类似下面的表格，

col1    col2    col3
1        2       3
2        1       3
3        2       1
1        4       6
4        6       1
6        4       1

在这里，我想对记录行进行排序。

预期输出。

col1    col2    col3
1        2       3
1        2       3
1        2       3
1        4       6
1        4       6
1        4       6

我使用了ASCII值进行比较。

declare @table表(col1 varchar(10)，col2 varchar(10)，col3 varchar(20))

insert into @tab
select '4','6','1' union
select '6','4','1' union
select '1','2','3'  union
select '2','1','3'  union
select '3','1','2' union
select '4','2','3'  union
select '1','4','6' union

select '5','5','1' union
select '5','5','1' union

select 'a','2','2' union
select '2','a','2' union
select '2','2','a'



;with CTE as(
    Select Case When ascii(Col1) <=  ascii(Col2) And  ascii(Col1) <=  
                     ascii(Col3) Then  cast(Col1 as varchar)
                When ascii(Col2) <=  ascii(Col1) And  ascii(Col2) <=  
                     ascii(Col3) Then  cast(Col2 as varchar)
                Else cast(Col3 as varchar) END as  col1,
                case when ( ascii(col1) >=  ascii(col2) and  ascii(col2) >=  
                            ascii(col3)) or ( ascii(col3) >=  ascii(col2) and  
                            ascii(col2) >=  ascii(col1)) then cast(Col2 as 
                            varchar) 
               when ( ascii(col1) >=  ascii(col3) and  ascii(col3) >=  
                      ascii(col2)) or ( ascii(col2) >=  ascii(col3) and  
                      ascii(col3) >=  ascii(col1)) then  cast(Col3 as varchar)
               when ( ascii(col3) >=  ascii(col1) and  ascii(col1) >=  ascii(col2)) or ( ascii(col2) >=  ascii(col1) and  ascii(col1) >=  ascii(col3)) then cast(Col1 as varchar) end as col2,
                Case When  ascii(Col1) >=  ascii(Col2) And  ascii(Col1) >=  ascii(Col3) Then  cast(Col1 as varchar)
                When  ascii(Col2) >=  ascii(Col1) And  ascii(Col2) >=  ascii(Col3) Then  cast(Col2 as varchar)
                Else  cast(Col3 as varchar) END as col3
    From   @tab)

    select * from CTE

有没有最短的方法来实现这个过程？

Answer 1

这种按行排序的需求通常表明您的表可以从新结构中受益。你真正想要实现的是什么？通过规范化col1、col2和col3使其看起来更垂直可能会更好(这是下面“未透视”的CTE所强制的，但表应该首先看起来像那样)。

如果必须这样做，请考虑向表中添加一个行标识符(基本上是主键)。

declare @tab table(
    rowId int identity(1,1),
    col1 varchar(10),
    col2 varchar(10),
    col3 varchar(20)
);

insert @tab values
    ('4','6','1'),
    -- etc

然后，您可以避免一堆case语句，并更容易地扩展到三个以上的列，如下所示：

with

    unpivoted as (

        select       rowId, 
                     val,
                     ord = row_number() over(partition by rowId order by val)
        from         @tab
        cross apply  (values (col1), (col2), (col3)) ap (val)

    )

    select    rowId,
              col1 = [1],
              col2 = [2],
              col3 = [3]
    from      unpvioted
    pivot     (max(val) for ord in ([1],[2],[3])) piv

您可以在操作here中看到它。

Answer 2

使用ROW_NUMBER()的另一种方法如下所示

SELECT (SELECT x 
        FROM   (SELECT  x, 
                             Row_number() 
                               OVER( 
                                 ORDER BY x) rn 
                FROM   (VALUES(col1), 
                              (col2), 
                              (col3))f(x))t 
        WHERE  rn = 1) c1, 
       (SELECT x 
        FROM   (SELECT  x, 
                             Row_number() 
                               OVER( 
                                 ORDER BY x) rn 
                FROM   (VALUES(col1), 
                              (col2), 
                              (col3))f(x))t 
        WHERE  rn = 2) c2, 
       (SELECT x 
        FROM   (SELECT  x, 
                             Row_number() 
                               OVER( 
                                 ORDER BY x) rn 
                FROM   (VALUES(col1), 
                              (col2), 
                              (col3))f(x))t 
        WHERE  rn = 3) c3 
FROM   @table 

或者像下面这样使用嵌套的CTE。

  ;WITH cte1 
     AS (SELECT (SELECT Min(f) 
                 FROM   (VALUES (col1), 
                                (col2), 
                                (col3)) AS Fields(f)) m1, 
                * 
         FROM   @table), 
     cte2 
     AS (SELECT (SELECT COALESCE(Min(f), M1) AS M2
                 FROM   (VALUES (col1), 
                                (col2), 
                                (col3)) AS Fields(f) 
                 WHERE  f > m1) m2, 
                * 
         FROM   cte1), 
     cte3 
     AS (SELECT m1, 
                m2, 
                (SELECT COALESCE(Min(f),m2) as m3 
                 FROM   (VALUES (col1), 
                                (col2), 
                                (col3)) AS Fields(f) 
                 WHERE  f > m2) m3 
         FROM   cte2) 
SELECT * 
FROM   cte3 

Answer 3

如下所示的用例

select case when col1>col2>col3 then col1
        when col2>col3 then col2
        else col3 end as col1, -- this is the condition for first column

对于3列，你必须这样写