select model,sum(s1) as s1,sum(tx) as tx
from(
select isnull(a.model,b.model) as model
a.s1,b.tx
from A表 a right B表 b on a.model=b.model
) a order by model
from(
select isnull(a.model,b.model) as model
a.s1,b.tx
from A表 a right B表 b on a.model=b.model
) a order by model
select isnull(a.model,b.model) as model
a.s1,b.tx
from (select model,sum(s1) as s1 from A表 group by model) a right B表 b on a.model=b.model
请问isnull(a.model,b.model)是什么意思?
能把a333 0 c5423显示出来吗?(因为我没有在单位,没有办法试)
,isnull(a.s1,0),b.tx
from (select model,sum(s1) as s1 from A表 group by model) a right join B表 b on a.model=b.model因为我用的是右连接,在A表中是没有a333 0 c5423的
所以用isnull的目的就是让它在A表中没有时,显示B表的model
第二个isnull是没有此记录时显示为0,而不是原来的NULL
declare @a表 table(model varchar(10),s1 int)
insert into @a表
select 'a100',100
union all select 'a222',200
union all select 'a100',115declare @b表 table(model varchar(10),tx varchar(10))
insert into @b表
select 'a100','a1818'
union all select 'a222','b2323'
union all select 'a333','c5423'
--显示结果
select isnull(a.model,b.model) as model
,isnull(a.s1,0),b.tx
from (select model,sum(s1) as s1 from @A表 group by model) a
right join @B表 b on a.model=b.model
上面的执行结果:model s1 tx
---------- ----------- ----------
a100 215 a1818
a222 200 b2323
a333 0 c5423(所影响的行数为 3 行)