如何使用while循环遍历表并创建具有所需值的另一个表 [英] How to loop through table using while loop and create another table with values needed
问题描述
我有两个表: MainTable
和 MyTable
。 MyTable具有唯一的ControlNo和ID。我需要从 MainTable
首先添加 EffDate
到 MyTable
基于 ID
和 ControlNo
。
I have two tables: MainTable
and MyTable
. MyTable has unique ControlNo and ID. I need to add very first EffDate
from MainTable
to MyTable
based on ID
and ControlNo
.
为此,我需要查看 PreviousID
列,然后查看该上一个ID
在 ID
列中,依此类推。
For that I need to look at PreviousID
column, then see if that PreviousID
is in ID
column and so on.
所需的输出应如下所示:
下面是一个示例数据,该数据通过suppl获取正确的 EffDate
使用 ID
值。它有效,但是如何遍历整个 MainTable
,检索 ID
和 EffDate
到单独的表中,然后将该表连接到 MyTable
?
The below is an example with dummy data of getting proper EffDate
by supplying an ID
value. It works, but how can I loop through the whole MainTable
, retrieve ID
's and EffDate
into separate table, then join that table to MyTable
?
-- function returns PreviousID based on ID
CREATE FUNCTION [dbo].[GetPriorQuoteID](@ID varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @RetVal varchar(50)
SET @RetVal = NULL
SELECT TOP 1 @RetVal = MainTable.PreviousID
FROM MainTable
WHERE MainTable.ID = @ID
RETURN @RetVal
END
-- create sample table
IF OBJECT_ID('MainTable') IS NOT NULL DROP TABLE MainTable;
select 3333 as ControlNo, 'QuoteID3' as ID, 'QuoteID2' as PreviousID, '2020-08-25' as EffDate
into MainTable
union all select 2222 as COntrolNo, 'QuoteID2', 'QuoteID1', '2019-08-25'
union all select 1111 as COntrolNo, 'QuoteID1', NULL, '2018-08-25'
union all select 7777 as COntrolNo, 'QuoteID6', 'QuoteID5', '2020-02-10'
union all select 6666 as COntrolNo, 'QuoteID5', NULL, '2019-02-10'
select * from MainTable
DECLARE @PriorQuote varchar(50)
DECLARE @RetVal VARCHAR(50) = ''
DECLARE @ControlNo INT
DECLARE @ID varchar(50) = 'QuoteID3'
SELECT TOP 1 @ControlNo = MainTable.ControlNo FROM MainTable WHERE MainTable.ID = @ID
Set @PriorQuote = @ID
SELECT TOP 1 @PriorQuote = MainTable.ID FROM MainTable WHERE MainTable.ControlNo = @ControlNo
WHILE dbo.GetPriorQuoteID(@PriorQuote) IS NOT NULL AND dbo.GetPriorQuoteID(@PriorQuote)<> @PriorQuote
BEGIN
SET @PriorQuote = dbo.GetPriorQuoteID(@PriorQuote)
END
SELECT TOP 1 @RetVal = CONVERT(VARCHAR(10), MainTable.EffDate, 101)
FROM MainTable
WHERE MainTable.ID = @PriorQuote
SELECT @RetVal
-- clean up
drop table MainTable
drop function GetPriorQuoteID
更新:添加虚拟数据表
-- create sample table #MainTable
IF OBJECT_ID('tempdb..#MainTable') IS NOT NULL DROP TABLE #MainTable;
create table #MainTable (ControlNo int, ID varchar(50), PreviousID varchar(50), EffDate date)
insert into #MainTable values
(3333,'QuoteID3','QuoteID2', '2020-08-25'),
(2222,'QuoteID2','QuoteID1', '2019-08-25'),
(1111,'QuoteID1',NULL, '2018-08-25'),
(7777,'QuoteID6','QuoteID5', '2020-02-10'),
(6666,'QuoteID5',NULL, '2019-02-10')
--select * from #MainTable
-- create sample table #MyTable
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable;
create table #MyTable (ControlNo int, ID varchar(50), EffDate date)
insert into #MyTable values
(3333,'QuoteID3',NULL),
(7777,'QuoteID6',NULL)
--select * from #MyTable
推荐答案
您可以使用递归查询遍历层次结构。
You can use a recursive query to traverse the hierarchy.
我将从将原始表与主表连接开始,这将路径限制为仅行我们感兴趣。然后,您可以向父级递归。最后,我们需要对每个路径的顶级父项进行过滤: top()
和 row_number()
对此非常有用
I would start by joining the original table with the main table, which restricts the paths to just the rows we are interested in. Then, you can recurse towards the parent. Finally, we need to filter on the top parent per path: top()
and row_number()
come handy for this.
考虑:
with cte as (
select t.controlno, t.id, m.previousid, m.effdate, 1 lvl
from #maintable m
inner join #mytable t on t.controlno = m.controlno and t.id = m.id
union all
select c.controlno, c.id, m.previousid, m.effdate, c.lvl + 1
from cte c
inner join #maintable m on m.id = c.previousid
)
select top(1) with ties controlno, id, effdate
from cte
order by row_number() over(partition by controlno, id order by lvl desc)
Demo on DB Fiddle:
controlno | id | effdate
--------: | :------- | :---------
3333 | QuoteID3 | 2018-08-25
7777 | QuoteID6 | 2019-02-10
这篇关于如何使用while循环遍历表并创建具有所需值的另一个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!