通过加入其他表格并在每个组中选择@ROW_NUMBER并重置它

My question title might not be meant exactly of what I mean but I didn't know what to write tbh.

我的问题标题可能并不完全是指我的意思，但我不知道该怎么写tbh。

So let's get into the question. What I want to have is like the following

让我们进入问题。我想拥有的是如下

Table1

表格1

+---------+---------+
| Column1 | Column2 |
+---------+---------+
| Code1   | LCode1  |
| Code2   | LCode2  |
| Code3   | LCode3  |
+---------+---------+

Table2

表2

+---------+---------+
| Column3 | Column4 |
+---------+---------+
| LCode1  | string1 |
| LCode1  | string2 |
| LCode1  | string3 |
| LCode2  | String1 |
| LCode2  | String2 |
| LCode2  | String3 |
| LCode2  | String4 |
| LCode3  | String1 |
| LCode3  | String2 |
+---------+---------+

Selected Table should be like that...

选择表应该是那样的......

+-------+---------+-----------+---------+
| Index | Column1 | Column2-3 | Column4 |
+-------+---------+-----------+---------+
|     0 | Code1   | LCode1    | string1 |
|     1 | Code1   | LCode1    | string2 |
|     2 | Code1   | LCode1    | string3 |
|     0 | Code2   | LCode2    | string1 |
|     1 | Code2   | LCode2    | string2 |
|     2 | Code2   | LCode2    | string3 |
|     3 | Code2   | LCode2    | string4 |
|     0 | Code3   | LCode3    | string1 |
|     1 | Code3   | LCode3    | string1 |
+-------+---------+-----------+---------+

I have tried to use ROW_NUMBER with a join statements between both tables but sadly I got no luck with that. It's only possible when I do use WHERE statement for specified Column2-3 value not all of them. As example

我试图在两个表之间使用ROW_NUMBER和连接语句，但遗憾的是我没有运气。只有当我对指定的Column2-3值使用WHERE语句而不是所有它们时才有可能。例如

SELECT ROW_NUMBER() AS Index, * FROM Table1 T1 JOIN Table2 T2 ON T1.Column2 = T2.Column3 Order By Index

Thanks in advance.

提前致谢。

2 个解决方案

#1

You appear to want:

你似乎想要：

SELECT ROW_NUMBER() OVER (PARTITION BY T1.column1 ORDER BY T2.column4) - 1 AS seqnum,
       T1.*, T2.*
FROM Table1 T1 JOIN
     Table2 T2
     ON T1.Column2 = T2.Column3
ORDER BY seqnum;

index is a bad name for a column because it is a SQL keyword.

index是列的错误名称，因为它是SQL关键字。

#2

Try to you use PARTITION BY in you query

尝试在查询中使用PARTITION BY

I hope could helped you.

我希望能帮助你。

#1