是否可以编写查询并获得所需的结果，如下所示

问题描述：

- 情景

table1

--Scenario

table1

id	data1	data2
1	100	150
1	200	250
1	300	350

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.

是否可以编写查询并获得所需的结果，如下所示

相关推荐