请帮我输出 [英] Please help me with output

问题描述

以下是table1



id1 id2 address1 address2

1 3 e f

1 5 i j

1 6 k l





以下是表2



id1 id2地址

1 1 NULL

1 2 NULL

1 3 NULL

1 4 NULL

1 5 NULL

1 6 NULL

1 7 NULL



现在我想用表1更新表格。



输出应如下所示：

id1 id2 address

1 1 e

1 2

1 3 f

1 4我

1 5 j

1 6 l

1 7 k


感谢您的建议。



谢谢，

Sreeram。

The below is table1

id1 id2 address1 address2
1 3 e f
1 5 i j
1 6 k l


The below is table 2

id1 id2 address
1 1 NULL
1 2 NULL
1 3 NULL
1 4 NULL
1 5 NULL
1 6 NULL
1 7 NULL

Now i want to update table 2 with table 1.

The output should be as below:
id1 id2 address
1 1 e
1 2 e
1 3 f
1 4 i
1 5 j
1 6 l
1 7 k

Thankful for your suggestions.

Thanks,
Sreeram.

推荐答案

这是一个奇怪的场景。首先，分享样本数据的最佳方式是以适当的方式分享它，这将有助于我们识别数据。



这样的东西：

Quite a bizarre scenario. 1st thing 1st, best way to share your sample data is to share it in a proper manner it will help us to recognize the data.

Something like this :

Declare @Table1 table (
					id1 int,
					id2 int,
					address1 varchar(20),
					address2  varchar(20)
					)

insert into @Table1
select 1, 3, 'e','f'	union all
select 1, 5, 'i','j'	union all
select 1, 6, 'k','l'


Declare @Table2 table (
					id1 int,
					id2 int,
					address1 varchar(20)
					)


insert into @Table2
select 1, 1,'NULL'  union all  
select 1, 2,'NULL'	union all
select 1, 3,'NULL'	union all
select 1, 4,'NULL'	union all
select 1, 5,'NULL'	union all
select 1, 6,'NULL'	union all
select 1, 7,'NULL';

你应该这样做练习所需的输出。无论如何，放手解决。查询将适用于SQL SERVER 2012及更高版本。



注意：此解决方案完全基于样本数据



以下是解决方案：

you should do the same practice for desired output. Anyways let go for the solution. Query will working on SQL SERVER 2012 and above version.

Note: this solution is solely based on sample data

Following is the solution:

Select t2.id1, t2.id2, case when t1.id2 = t2.id2 then t1.address2 else t1.address1 end
from 
(
	Select M.id1, M.id2, M.address1, M.address2, case when previd is null then m.id1 else previd + 1 end as startID
			,case when M.id2 = lastId and M.id2 < rng.maxid2 then rng.maxid2 else M.id2 end as endID
	from (
		Select t1.id1, t1.id2, t1.address1, t1.address2
			, LAG(t1.id2 , 1) OVER (Partition by t1.id1 order by t1.id1 ) as previd
			, LAST_VALUE(t1.id2)OVER (Partition by t1.id1 order by t1.id1 ) as lastId
		from @Table1 t1
	) M
	join 
	( 
		Select t2.id1, max(t2.id2) AS maxid2
		from @Table2 t2
		Group by t2.id1
	) rng on m.id1 = rng.id1

) t1
join @Table2 t2 on t1.id1 = t2.id1 and t2.id2 between t1.startID and t1.endID

希望它有所帮助。

hope it helps.

这篇关于请帮我输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！