是否可以编写查询并获得所需的结果,如下所示 [英] Is it possible to write a query and get the desired result as below
本文介绍了是否可以编写查询并获得所需的结果,如下所示的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
- 情景
table1
--Scenario
table1
id data1 data2
1 100 150
1 200 250
1 300 350
< u> table2
table2
id value1 value2
1 1000 1050
i want the result in this way using join. Is it possible ?
id data1 data2 value1 value2
1 100 150 1000 1050
1 200 250 null null
1 300 350 null null
- 即时通讯尝试这种方式但不是上述结果...
-- i m trying this way but not as above result...
select *
from #temp5 t1
left join #temp6 t2 on t1.id=t2.id
where t1.id=1
推荐答案
试试这个,
Try this,
select t1.id,t1.data1,t1.data2,t2.value1,t2.value2 from table2 t2
right join
table1 t1 on t1.id = 1;
结果
--------------
Result
--------------
id data1 data2 value1 value2
1 100 150 1000 1050
2 200 250 null null
3 300 350 null null
请注意,你不能拥有与你提到的相同的身份。
Notice here you can not have same id as you have mentioned.
试试这个
Try This
declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)
insert into @t(id, data1, data2)
values(1, 100, 150)
insert into @t(id, data1, data2)
values(1, 200, 250)
insert into @t(id, data1, data2)
values(1, 300, 350)
insert into @b(id, value1, value2)
values(1, 1000, 1050)
select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
left join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr
; with c as
(
select userid,fromdate,todate, row_number() over (order by userid) r from tbl_LMS_Data with(nolock)
where userid= @USERID and fromdate > dateadd(dd,-30,getdate())
),e as
(
select userid,fromdate as fromdatetas ,todate as todatetas, row_number() over (order by userid) r from tbl_tas_data with(nolock) where userid= @USERID
and fromdate > dateadd(dd,-30,getdate())
)
select c.userid,c.fromdate,c.todate,fromdatetas,todatetas
into #temp2
from c
full outer join e on c.r = e.r
order by c.userid desc
两个表是tbl_lms_data [table1]和tbl_tas_data [表2]。所以这很有效。
two tables are tbl_lms_data [table1] and tbl_tas_data[table2]. So this works perfectly.
这篇关于是否可以编写查询并获得所需的结果,如下所示的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文