___________________
| 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
)
我可以做这样的事情吗?
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
编辑(以发表评论):
如果您不想使用a,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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句