使用大量链接/连接一次迭代 1 行

发布于 2024-08-07 12:28:28 字数 730 浏览 10 评论 0原文

好吧，基本上需要的是一种在使用大量联接并使用这些行号的 where 子句的同时获得行号的方法。

类似 ROWNUMBER 的内容

select ADDRESS.ADDRESS FROM ADDRESS 
INNER JOIN WORKHISTORY ON WORKHISTORY.ADDRESSRID=ADDRESS.ADDRESSRID
INNER JOIN PERSON ON PERSON.PERSONRID=WORKHISTORY.PERSONRID
WHERE PERSONRID=<some number> AND WORKHISTORY.ROWNUMBER=1

例如，需要为该表上的此查询生成。因此，如果我们想要访问第二个 WORKHISTORY 记录的地址，我们可以直接使用 WORKHISTORY.ROWNUMBER=2 并且如果我们有两个匹配的地址，我们可以使用循环遍历一个 WORKHISTORY 记录的地址ADDRESS.ROWNUMBER=1 和 ADDRESS.ROWNUMBER=2

这应该能够成为自动生成的查询。因此，可能有超过 10 个内部联接才能到达相关表，并且我们需要能够独立于其余表循环遍历每个表的记录。

我知道有 RANK 和 ROWNUMBER函数，但由于所有内部联接，我不知道它如何为我工作

注意：在这个示例查询中， ROWNUMBER 应该自动生成！它不应该存储在实际的表中

原文

Ok, basically what is needed is a way to have row numbers while using a lot of joins and having where clauses using these rownumbers.

such as something like

select ADDRESS.ADDRESS FROM ADDRESS 
INNER JOIN WORKHISTORY ON WORKHISTORY.ADDRESSRID=ADDRESS.ADDRESSRID
INNER JOIN PERSON ON PERSON.PERSONRID=WORKHISTORY.PERSONRID
WHERE PERSONRID=<some number> AND WORKHISTORY.ROWNUMBER=1

ROWNUMBER needs to be generated for this query on that one table though. So that if we want to access the second WORKHISTORY record's address, we could just go WORKHISTORY.ROWNUMBER=2 and if say we had two address's that matched, we could cycle through the addresses for one WORKHISTORY record using ADDRESS.ROWNUMBER=1 and ADDRESS.ROWNUMBER=2

This should be capable of being an automatically generated query. Thus, there could be more than 10 inner joins in order to get to the relevant table, and we need to be able to cycle through each table's record independently of the rest of the tables..

I'm aware there is the RANK and ROWNUMBER functions, but I'm not seeing how it will work for me because of all the inner joins

note: in this example query, ROWNUMBER should be automatically generated! It should never be stored in the actual table

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故人如初 2024-08-14 12:28:28

你可以使用临时表吗？

我问是因为你可以这样编写代码：

select a.field1, b.field2, c.field3, identity (int, 1,1)  as TableRownumber into #temp
from table1 a 
join table2 b on a.table1id = b.table1id
join table3 c on b.table2id = c.table2id

select * from #temp where ...

Can you use a temp table?

I ask because you can write the code like this:

select a.field1, b.field2, c.field3, identity (int, 1,1)  as TableRownumber into #temp
from table1 a 
join table2 b on a.table1id = b.table1id
join table3 c on b.table2id = c.table2id

select * from #temp where ...

回复收藏 0 原文

~没有更多了~