日期:2014-05-17 浏览次数:20653 次
if OBJECT_ID('xmxxb') is not null drop table xmxxb
create table xmxxb
(
number int identity,
cbr varchar(20),
xmmc varchar(20)
)
if OBJECT_ID('cbrbgqkb') is not null drop table cbrbgqkb
create table cbrbgqkb
(
number int,
bgqcbr varchar(20),
bghcbr varchar(20)
)
insert into xmxxb
select '张三','项目1' union
select '李四','项目2' union
select '王武','项目3' union
select '王启','项目4'
insert into cbrbgqkb
select 1,'王武','李四' union
select 1,'李四','张三' union
select 2,'王武','李四' union
select 3,'张三','王武'
with cte
as
(
select t.cbr,t.调进数,t1.调出数
from
(
select x.cbr,count(t.bghcbr) 调进数
from
xmxxb x left join
cbrbgqkb t on x.cbr=t.bghcbr
group by x.cbr
) t
full join
(
select x.cbr,count(t.bgqcbr) 调出数
from
xmxxb x left join
cbrbgqkb t&n