如何显示3表的最新状态 [英] How to show latest status from 3 table
问题描述
你好编码器,
<前lang =C#>他们是三个表Reference1,Reference2和Reference3
< pre lang = text> Reference1
123 等待
234 等待
345 待定
456 待定
参考2
345转
456转
参考3
456完成
现在我想要像
参考1
123等待
234等待
345
$ 46完成
我尝试了什么:
我使用下面的查询,但它会给出我
456完成
SELECT Reference3。*
FROM Reference1
JOIN参考2
ON Reference2.ReferenceNumber = Reference1.ReferenceNumber
JOIN参考3
ON Reference3.ReferenceNumber = Reference2.ReferenceNumber
您好,
首先我创建三个临时表进行解释。
create table #tmp1
(
referenceno varchar ( 10 ),
statuscode varchar ( 20 ),
)
创建 表 #tmp2
(
referenceno varchar ( 10 ),
statuscode varchar ( 20 ),
)
创建 table #tmp3
(
referenceno varchar ( 10 ),
statuscode varchar ( 20 ),
)
- 现在插入数据
插入 进入# tmp1 值(' 123',< span class =code-string>' pending' )
insert into #tmp1 values (' 234',' pending')
insert into #tmp1 values (' 345',' pending')
插入 到 #tmp1 值(' 456',' 待定')
插入 进入 #tmp1 values (' 567',' pending)
insert 进入 #tmp1 值(' 678',' pending')
插入 进入 #tmp2 值( ' 345',' go')
插入 进入 #tmp2 值(' 456',' go')
insert into #tmp2 值(' 567',' go')
insert into #tmp3 values (' 456',' done')
插入 到 #tmp3 值(' 567',' 已完成')
插入 进入 #tmp3 值(' 678', ' 已完成')
获得所需的OP,首先将所有这三个表联合起来。
然后使用Row_Number和Partition by子句,按状态码逐个排序,最后使用Common Table Ex迫切需要获得OP。
; CTE
as
(
select ROW_NUMBER() OVER ( partition by referenceno ORDER < span class =code-keyword> BY statuscode) AS RowNumber,*
来自
(
选择 * 来自#tmp1
< span class =code-keyword> union all
select * < span class =code-keyword> from #tmp2
union all
选择 * 来自 #tmp3
)xx)
选择 referenceno,状态码来自 cte 其中 RowNumber = 1
OP
referenceno statuscode
123 待定
234 等待
345 进行
456 已完成
567 已完成
678 完成
这里有另一种可以帮助你而不使用CTE的解决方案
首先只选择#tmp1或第一个表中不存在于其他两个表中的记录(#tmp2和#tmp3)
再次选择#tmp2或second table中的那些记录不存在于
最后一个表(#tmp3)
,最后在这些记录集之间建立联合。
选择 referenceno,状态码来自# tmp1 其中 referenceno
(
选择 referenceno 来自#tmp2
union 全部
选择 referenceno 来自#tmp3
)
union all
选择 referenceno,statuscode 来自#tmp2 其中 referenceno
(
选择 referenceno 来自#tmp3
)
union 全部
选择 referenceno,状态码来自 #tmp3
这里也是OP
referenceno statuscode
123 待定
< span class =code-digit> 234 待定
345 进行
456 已完成
567 已完成
678 已完成
谢谢
Hi coder,
Their are three table Reference1, Reference2 and Reference3
<pre lang="text">Reference1
123 pending
234 pending
345 pending
456 pending
Reference2
345 going
456 going
Reference3
456 Done
Now I want out like
Reference1
123 pending
234 pending
345 going
456 Done
What I have tried:
I use below query but it will give me
456 Done
SELECT Reference3.*
FROM Reference1
JOIN Reference2
ON Reference2.ReferenceNumber = Reference1.ReferenceNumber
JOIN Reference3
ON Reference3.ReferenceNumber = Reference2.ReferenceNumber
Hello ,
First I create three temporary table for explanation .
create table #tmp1 ( referenceno varchar(10), statuscode varchar(20), ) create table #tmp2 ( referenceno varchar(10), statuscode varchar(20), ) create table #tmp3 ( referenceno varchar(10), statuscode varchar(20), )
--Now insert data insert into #tmp1 values('123', 'pending') insert into #tmp1 values('234', 'pending') insert into #tmp1 values('345', 'pending') insert into #tmp1 values('456', 'pending') insert into #tmp1 values('567', 'pending') insert into #tmp1 values('678', 'pending') insert into #tmp2 values('345', 'going') insert into #tmp2 values('456', 'going') insert into #tmp2 values('567', 'going') insert into #tmp3 values('456', 'done') insert into #tmp3 values('567', 'done') insert into #tmp3 values('678', 'done')
to get required OP , first make union all of those three tables .
and then use Row_Number with Partition by clause, to sequence them one by one by statuscode and lastly use Common Table Expression to get required OP .
;with CTE as ( select ROW_NUMBER() OVER (partition by referenceno ORDER BY statuscode) AS RowNumber , * from ( select * from #tmp1 union all select * from #tmp2 union all select * from #tmp3 )xx) select referenceno , statuscode from cte where RowNumber=1
OP
referenceno statuscode 123 pending 234 pending 345 going 456 done 567 done 678 done
Here another solution that may help you without using CTE
First Select only those records from #tmp1 or first table which are not present in other two tables (#tmp2 and #tmp3)
again select only those records from #tmp2 or second table which are not present in
last table (#tmp3)
and lastly make union all between those records sets .
select referenceno , statuscode from #tmp1 where referenceno not in ( select referenceno from #tmp2 union all select referenceno from #tmp3 ) union all select referenceno , statuscode from #tmp2 where referenceno not in ( select referenceno from #tmp3 ) union all select referenceno , statuscode from #tmp3
Here also OP is
referenceno statuscode 123 pending 234 pending 345 going 456 done 567 done 678 done
Thanks
这篇关于如何显示3表的最新状态的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!