求一条sql语句,关于外链表的解决方案

求一条sql语句,关于外链表的

SQL code

declare @t1 table(期间 int,科目编码 varchar(50),科目名称 varchar(50),c3 money)declare @t2 table(科目编码 varchar(50),科目名称 varchar(50))insert into @t1select 1,'1','科目名称1',100.55 union allselect 1,'2','科目名称2',120.0 union allselect 1,'3','科目名称3',20 union allselect 2,'3','科目名称4',54.21 union allselect 2,'4','科目名称4',5 insert into @t2select '1','科目名称1' union allselect '2','科目名称2' union allselect '3','科目名称3' union allselect '4','科目名称4' union allselect '5','科目名称5' select * from @t1select * from @t2/*想得到结果:期间 科目编码    科目名称        c31    1    科目名称1    100.551    2    科目名称2    120.01    3    科目名称3    201    4    科目名称4    NULL1    4    科目名称5    NULL2    1    科目名称1    NULL2    2    科目名称2    NULL2    3    科目名称3    54.212    4    科目名称4    52    4    科目名称5    NULL*/

[解决办法]
create table t1
(
a int,
b varchar(50),
c varchar(50),
d money
)
insert into t1
select 1,'1','科目名称1',100.55 union all
select 1,'2','科目名称2',120.0 union all
select 1,'3','科目名称3',20 union all
select 2,'4','科目名称4',54.21 union all
select 2,'4','科目名称4',5 
create table t2
(
b varchar(50),
c varchar(50)
)
insert into t2
select '1','科目名称1' union all
select '2','科目名称2' union all
select '3','科目名称3' union all
select '4','科目名称4' union all
select '5','科目名称5'
select * from t1
select * from t2

select b1.a as '期间',b1.b as '科目编码',b1.c as '科目名称',t1.d as 'c3' from 
(select a1.a as a,t2.* from t2 cross join (select distinct a from t1) as a1) as b1 left join t1 
on b1.a=t1.a and b1.b=t1.b and b1.c=t1.c

---------------------------------
期间科目编码科目名称c3
11科目名称1100.55
12科目名称2120.00
13科目名称320.00
14科目名称4NULL
15科目名称5NULL
21科目名称1NULL
22科目名称2NULL
23科目名称3NULL
24科目名称454.21
24科目名称45.00
25科目名称5NULL
[解决办法]

SQL code

create table t1(a int,b varchar(50),c varchar(50),d money)insert into t1select 1,'1','科目名称1',100.55 union allselect 1,'2','科目名称2',120.0 union allselect 1,'3','科目名称3',20 union allselect 2,'4','科目名称4',54.21 union allselect 2,'4','科目名称4',5  create table t2(b varchar(50),c varchar(50))insert into t2select '1','科目名称1' union allselect '2','科目名称2' union allselect '3','科目名称3' union allselect '4','科目名称4' union allselect '5','科目名称5'SELECT t1.a,t3.B,T3.C,t1.d FROM t1  FULL JOIN( SELECT * FROM  t2 ) T3ON t1.B =T3.b AND t1.c=T3.ca           B                                                  C                                                  d----------- -------------------------------------------------- -------------------------------------------------- ---------------------1           1                                                  科目名称1                                              100.551           2                                                  科目名称2                                              120.001           3                                                  科目名称3                                              20.002           4                                                  科目名称4                                              54.212           4                                                  科目名称4                                              5.00NULL        5                                                  科目名称5                                              NULL(6 行受影响) 

 

查看更多 下一篇