代码之家 › 专栏 › 技术社区 › Mano

按行排序

sql-server sql

-3

Mano · 技术社区 · 7 年前

我有一张像下面这样的桌子,

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@tab 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

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

3 回复 | 直到 7 年前

PSK 7 年前

另一种方法是 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

Online Demo

pwilcox 7 年前

这种按行排序的需要通常是表可以从新结构中受益的标志。你到底想完成什么?规范化col1、col2和col3以使其看起来更垂直(这正是下面的“unpivoted”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 .

Zaynul Abadin Tuhin 7 年前

用例如下

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个栏目

Dev 7 年前

如果所有列都是整数类型,则下面的查询将起作用,

SELECT c1 = CASE
  WHEN c1 <= c2 AND c1 <= c3 THEN c1
  WHEN c2 <= c1 AND c2 <= c3 THEN c2
  ELSE c3 END,
c2 = CASE 
  WHEN c1 <= c2 AND c1 <= c3 THEN 
    CASE WHEN c2 <= c3 THEN c2 ELSE c3 END
  WHEN c2 <= c1 AND c2 <= c3 THEN
    CASE WHEN c1 <= c3 THEN c1 ELSE c3 END
  ELSE
    CASE WHEN c1 <= c2 THEN c1 ELSE c2 END
  END,
c3 = CASE
  WHEN c1 >= c2 AND c1 >= c3 THEN c1
  WHEN c2 >= c1 AND c2 >= c3 THEN c2
  ELSE c3 END
FROM temp_x;

如果有任何字符,则可以使用ASCII值进行cpompare。

Gordon Linoff 7 年前

最快的方法可能是 case 表达式,但这并不能泛化。

我愿意 apply 作为性能和可扩展性之间的良好平衡:

select v.*
from t cross apply
     (select max(case when seqnum = 1 then col end) as col1,
             max(case when seqnum = 2 then col end) as col2,
             max(case when seqnum = 3 then col end) as col3             
      from (select v.*,
                   row_number() over (order by col) as seqnum
            from (values (t.col1), (t.col2), (t.col3)) v(col)
           ) v
     ) v;

这也很容易处理 NULL 价值观和纽带,使 案例 接近。