sql - 在同一字段上选择升序和降序

Question

___________________
| field1 | field1 |
|________|________|
|    A   |   E    |
|    B   |   D    |
|    C   |   C    |
|    D   |   B    |
|    E   |   A    |
|________|________|

如果可能的话，我会像使用相同字段的同一个表一样进行 sql 选择吗？

select t1.c, t2.c from
(
    WITH cte AS
    (
        SELECT 
            c,
            ROW_NUMBER() OVER(ORDER BY c DESC) AS n,
            ROW_NUMBER() OVER(ORDER BY c ASC) AS m
        FROM @t
    )
    SELECT t1.c, t2.c
    FROM cte t1
    JOIN cte t2 ON t2.n = t1.m
)

我可以做这样的事情吗？

Answer 1

DECLARE @t TABLE ( c CHAR(1) )

INSERT INTO @t
VALUES ('a'),('b'),('c'),('d'),('e')

;WITH cte AS
(
    SELECT 
        c,
        ROW_NUMBER() OVER(ORDER BY c DESC) AS n,
        ROW_NUMBER() OVER(ORDER BY c ASC) AS m
    FROM @t
)

SELECT t1.c, t2.c
FROM cte t1
JOIN cte t2 ON t2.n = t1.m

编辑(评论):

如果您不想使用 CTE那么你会把它写成

SELECT t1.c, t2.c 
FROM ( 
    SELECT c
        , ROW_NUMBER() OVER(ORDER BY c ASC) AS m 
    FROM @t 
) t1
JOIN ( 
    SELECT c
        , ROW_NUMBER() OVER(ORDER BY c DESC) AS n
    FROM @t 
) t2 ON t2.n = t1.m

SQLFiddle Demo