如何对字段进行多次排序？

Question

我有一个SQL Server表业余爱好，如下所示：

id        hobby        skills          organization
-------------------------------------------------------
1         reading      encoding        greenpeace
2         cooking      web design      red cross
3         baking       programming     convoy of hope

我想要一个查询，结果如下：

 hobby        skills          organization
------------------------------------------------
 reading      programming     greenpeace
 cooking      web design      red cross
 baking       encoding        convoy of hope

爱好按提升中的id分类，技能在降序中按id排序，最后在提升中按id排序。

有可能这样做吗？

Answer 1

这是一个非常奇怪的数据组织，但如果您坚持，可以这样做，例如，使用ROW_NUMBER函数。

示例数据-我添加了您在注释中提到的employeenumber。

DECLARE @T TABLE (
id int, 
hobby nvarchar(255), 
skills nvarchar(255), 
organization nvarchar(255), 
employeenumber int);

INSERT INTO @T VALUES
(11, 'reading', 'encoding   ', 'greenpeace    ', 123),
(12, 'cooking', 'web design ', 'red cross     ', 123),
(13, 'baking ', 'programming', 'convoy of hope', 123),
(21, 'reading', 'encoding   ', 'greenpeace    ', 222),
(22, 'cooking', 'web design ', 'red cross     ', 222),
(23, 'baking ', 'programming', 'convoy of hope', 222);

查询

WITH
CTE
AS
(
    SELECT
        id
        ,hobby
        ,skills
        ,organization
        ,ROW_NUMBER() OVER (ORDER BY id ASC) AS rn_asc
        ,ROW_NUMBER() OVER (ORDER BY id DESC) AS rn_desc
    FROM @T AS hobbies
    WHERE employeenumber = 222
)
SELECT
    C1.id
    ,C1.hobby
    ,C2.skills
    ,C1.organization
FROM
    CTE AS C1
    INNER JOIN CTE AS C2 ON C2.rn_desc = C1.rn_asc
ORDER BY C1.rn_asc;

结果

+----+---------+-------------+----------------+
| id |  hobby  |   skills    |  organization  |
+----+---------+-------------+----------------+
| 21 | reading | programming | greenpeace     |
| 22 | cooking | web design  | red cross      |
| 23 | baking  | encoding    | convoy of hope |
+----+---------+-------------+----------------+

Answer 2

桌子不是这样工作的。你的每一个元组(id，业馀爱好，技能，组织)构成一排。您可以更改(整行)的顺序，但不能在任意条件下独立地对列进行排序。

Answer 3

这不是表的工作方式，但可以做到这一点。一种方法是聚合和union all。

select max(hobby) as hobby, max(skills) as skills,
       max(organization) as organization
from ((select row_number() over (order by id asc) as seqnum, hobby, NULL as skills, organization
       from hobbies
      ) union all
      (select row_number() over (order by id desc), NULL, skills, NULL
       from hobbies
      )
     ) h
group by seqnum
order by seqnum;