表A code name COD a SO2 b NO2 c表B EnterName code 2010 2011 a COD 1 2 b SO2 1 3结果 EnterName code 2010 2011 a COD 1 2 a SO2 0 0 a N02 0 0 b COD 1 3 b S02 0 0 b N02 0 0意思就是把表B中缺少的对应的表A(字典表)里的一一补上。 谢谢~~!
结果刚写错了,重新补上结果 EnterName code 2010 2011 a COD 1 2 a SO2 0 0 a N02 0 0 b COD 0 0 b S02 1 3 b N02 0 0 谢谢~~
select a.name as EnterName,a.code,isnull(b.[2010],0),isnull(b.[2011],0) from 表A a left join 表b b on a.name+a.code=b.EnterName+b.code where exists(select 1 from 表b where a.name=b.EnterName )
declare @表A table (code varchar(3),name varchar(1)) insert into @表A select 'COD','a' union all select 'SO2','b' union all select 'NO2','c'declare @表B table (EnterName varchar(1),code varchar(3),[2010] int,[2011] int) insert into @表B select 'a','COD',1,2 union all select 'b','SO2',1,3 select isnull(bb.EnterName,aa.EnterName), isnull(bb.code,aa.code), isnull([2010],0), isnull([2011],0) from ( select a.code,b.EnterName from @表A a cross join @表B b ) aa left join @表B bb on aa.code=bb.code and aa.EnterName=bb.EnterName order by aa.EnterName /* a COD 1 2 a SO2 0 0 a NO2 0 0 b COD 0 0 b SO2 1 3 b NO2 0 0 */
declare @表A table (code varchar(3),name varchar(1)) insert into @表A select 'COD','a' union all select 'SO2','b' union all select 'NO2','c'declare @表B table (EnterName varchar(1),code varchar(3),[2010] int,[2011] int) insert into @表B select 'a','COD',1,2 union all select 'b','SO2',1,3select EnterName=isnull(bb.EnterName,aa.EnterName), code=isnull(bb.code,aa.code),[2010]=isnull([2010],0),[2010]=isnull([2011],0) from (select a.code,b.EnterName from @表A a cross join @表B b) aa left join @表B bb on aa.code=bb.code and aa.EnterName=bb.EnterName order by aa.EnterName /* EnterName code 2010 2010 --------- ---- ----------- ----------- a COD 1 2 a SO2 0 0 a NO2 0 0 b COD 0 0 b SO2 1 3 b NO2 0 0 */
谢谢你的回答,但是结果有错误 比如我在表B中增加一条记录 union all select 'b','NO2',1,3 出来的结果不对,多出了好多的记录表A中的name和表B中的EnterName是没有关系的
select isnull(bb.EnterName,aa.EnterName) as EnterName, isnull(bb.code,aa.code) as code,isnull([2010],0) as [2010], isnull([2011],0) as [2010] from (select a.code,b.EnterName from a ,b)a left join b on a.code=b.code and a.EnterName=b.EnterName order by a.EnterName
EnterName code 2010 2011
a COD 1 2
a SO2 0 0
a N02 0 0
b COD 0 0
b S02 1 3
b N02 0 0
谢谢~~
left join 表b b on a.name+a.code=b.EnterName+b.code
where exists(select 1 from 表b where a.name=b.EnterName )
declare @表A table (code varchar(3),name varchar(1))
insert into @表A
select 'COD','a' union all
select 'SO2','b' union all
select 'NO2','c'declare @表B table (EnterName varchar(1),code varchar(3),[2010] int,[2011] int)
insert into @表B
select 'a','COD',1,2 union all
select 'b','SO2',1,3
select isnull(bb.EnterName,aa.EnterName),
isnull(bb.code,aa.code),
isnull([2010],0),
isnull([2011],0)
from (
select a.code,b.EnterName from @表A a cross join @表B b
) aa left join @表B bb
on aa.code=bb.code and aa.EnterName=bb.EnterName
order by aa.EnterName
/*
a COD 1 2
a SO2 0 0
a NO2 0 0
b COD 0 0
b SO2 1 3
b NO2 0 0
*/
declare @表A table (code varchar(3),name varchar(1))
insert into @表A
select 'COD','a' union all
select 'SO2','b' union all
select 'NO2','c'declare @表B table
(EnterName varchar(1),code varchar(3),[2010] int,[2011] int)
insert into @表B
select 'a','COD',1,2 union all
select 'b','SO2',1,3select EnterName=isnull(bb.EnterName,aa.EnterName),
code=isnull(bb.code,aa.code),[2010]=isnull([2010],0),[2010]=isnull([2011],0)
from (select a.code,b.EnterName from @表A a cross join @表B b) aa
left join @表B bb
on aa.code=bb.code and aa.EnterName=bb.EnterName
order by aa.EnterName
/*
EnterName code 2010 2010
--------- ---- ----------- -----------
a COD 1 2
a SO2 0 0
a NO2 0 0
b COD 0 0
b SO2 1 3
b NO2 0 0
*/
谢谢你的回答,但是结果有错误
比如我在表B中增加一条记录
union all
select 'b','NO2',1,3
出来的结果不对,多出了好多的记录表A中的name和表B中的EnterName是没有关系的
isnull(bb.EnterName,aa.EnterName) as EnterName,
isnull(bb.code,aa.code) as code,isnull([2010],0) as [2010],
isnull([2011],0) as [2010]
from
(select a.code,b.EnterName from a ,b)a
left join b
on
a.code=b.code
and
a.EnterName=b.EnterName
order by
a.EnterName
表A不增加,表B增加比如现在增加EnterName为c的记录,则记录中肯定应该增加3条记录,总共为9条
如果增加EnterName为b,即表B中增加'b','NO2',1,3,记录肯定还是6条
只是值不为0了而已
这样应该说明白了吧,谢谢~