如何在 sql server 中对行号进行内部联接

How to do an inner join on row number in sql server(如何在 sql server 中对行号进行内部联接)

问题描述

SQL Server 2008

SQL Server 2008

两张桌子:

表A有以下数据:

RowA
RowB
RowC
RowD

表 B 有以下数据:

Row4
Row3
Row2
Row1

我想得到以下输出:

RowA Row1
RowB Row2
RowC Row3
RowD Row4

两个表唯一的共同值是行号

The only common value between the two tables is the row number

我当然可以单独获取数据:

I can get the data individually of course:

SELECT val
FROM A
ORDER BY val

SELECT val
FROM B
ORDER BY val

但是我如何加入行号?

如果我没有 order-by，但只想要按它们出现的顺序排列的行怎么办?

And what if I don't have an order-by, but just want the rows in the order they come out?

RowA Row4
RowB Row3
RowC Row2
RowD Row1

如在加入

SELECT val
FROM A

SELECT val
FROM B

推荐答案

试试这个:

在 sql server 2008 中使用 ROW_NUMBER() 函数

USE ROW_NUMBER() function in sql server 2008

select A.val,B.val 
from(
    SELECT val,row_number() over (order by val) as row_num
    FROM A)A
join
    (SELECT val,row_number() over (order by val) as row_num
    FROM B)B
on  A.row_num=B.row_num
ORDER BY A.val,B.val

这篇关于如何在 sql server 中对行号进行内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！