sql 承办人变更情况统计问题求助

[CHQIUU]

表1 项目信息表 xmxxb
number cbr xmmc
1 张三 项目1
2 李四 项目2
3 王武 项目3
4 王启 项目4

表2 承办人变更情况表 cbrbgqkb
number bgqcbr bghcbr
1 王武 李四
1 李四 张三
2 王武 李四
3 张三 王武

现在需要统计出各人员的承办项目数，从本人身上调整出去的项目数量，从别人调整过来的项目数量
显示结果为：
承办人 承办项目数 调进数 调出数
张三 1 1 1
李四 1 2 1
王武 1 1 2
王启 1 0 0

[CHQIUU]

引用 5 楼 daiyueqiang 的回复:

select t.cbr 承办人,COUNT(t.number) 承办项目数,COUNT(t1.bghcbr) 调进数,COUNT(t2.bgqcbr) 调出数
from
xmxxb t left join cbrbgqkb t1 on t.cbr=t1.bghcbr
left join cbrbgqkb t2 on t.cbr=t2.bgqcbr
group by t.cbr

这样统计的结果不对啊

[daiyueqiang2045]

引用 6 楼 QIU176161650 的回复:

[quote=引用 5 楼 daiyueqiang 的回复:]

select t.cbr 承办人,COUNT(t.number) 承办项目数,COUNT(t1.bghcbr) 调进数,COUNT(t2.bgqcbr) 调出数
from
xmxxb t left join cbrbgqkb t1 on t.cbr=t1.bghcbr
left join cbrbgqkb t2 on t.cbr=t2.bgqcbr
group by t.cbr

这样统计的结果不对啊 [/quote] 那先用我最上边的办法，现在还没有想到更效率的。嗨！

[gogodiy]

CREATE TABLE t1
(
	number INT,
	cbr VARCHAR(10),
	xmmc VARCHAR(10)
)
INSERT INTO t1
SELECT 1,'张三','项目1' UNION ALL
SELECT 2,'李四','项目2' UNION ALL 
SELECT 3,'王武','项目3' UNION ALL
SELECT 4,'王启','项目4'
CREATE TABLE t2
(
	number INT,
	bgqcbr VARCHAR(10),	--变更前
	bghcbr VARCHAR(10),	--变更后
)
INSERT INTO t2
SELECT 1,'王武','李四' UNION ALL
SELECT 1,'李四','张三' UNION ALL
SELECT 2,'王武','李四' UNION ALL
SELECT 3,'张三','王武'
SELECT * FROM t1
SELECT * FROM t2

;WITH AAA AS
(
	SELECT	bgqcbr,
			COUNT(bgqcbr) AS total1
	FROM	t2
	GROUP BY bgqcbr
)

SELECT	A.cbr AS [承办人],
		ISNULL(D.total3,0) AS [承办项目数],
		ISNULL(C.total2,0) AS [调进数],
		ISNULL(B.total1,0) AS [调出数]
FROM	t1 AS A WITH(NOLOCK) LEFT JOIN
		(
			SELECT	bgqcbr,
					COUNT(bgqcbr) AS total1
			FROM	t2
			GROUP BY bgqcbr
		) AS B ON A.cbr=B.bgqcbr LEFT JOIN
		(
			SELECT	bghcbr,
					COUNT(bghcbr) AS total2
			FROM	t2
			GROUP BY bghcbr
		) AS C ON A.cbr=C.bghcbr LEFT JOIN
		(
			SELECT	cbr,
					COUNT(xmmc) AS total3
			FROM	t1
			GROUP BY cbr
		) AS D ON A.cbr=D.cbr

承办人 承办项目数 调进数 调出数 张三 1 1 1 李四 1 2 1 王武 1 1 2 王启 1 0 0

[CHQIUU]

有哪位大侠能够解决啊？非常感谢

[asdf147asdf]

select cbr as [承办人],count(*) as [承办项目数], (select count(*) from cbrbgqkb where bghcbr=a.cbr) as [调进数], (select count(*) from cbrbgqkb where bgqcbr=a.cbr) as [调出数] from xmxxb a

[CHQIUU]

引用 2 楼 asdf147asdf 的回复:

select cbr as [承办人],count(*) as [承办项目数], (select count(*) from cbrbgqkb where bghcbr=a.cbr) as [调进数], (select count(*) from cbrbgqkb where bgqcbr=a.cbr) as [调出数] from xmxxb a

这样执行起来效率不较低，有没有更好的办法

[daiyueqiang2045]

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 on x.cbr=bgqcbr
	group by x.cbr
	) t1
	on t.cbr=t1.cbr
)
select t.cbr 承办人,COUNT(t.number) 承办项目数,min(cte.调进数) 调进数,min(cte.调出数)调出数
from
xmxxb t inner join cte on t.cbr=cte.cbr
group by t.cbr

[daiyueqiang2045]

select t.cbr 承办人,COUNT(t.number) 承办项目数,COUNT(t1.bghcbr) 调进数,COUNT(t2.bgqcbr) 调出数
from
xmxxb t left join cbrbgqkb t1 on t.cbr=t1.bghcbr
left join cbrbgqkb t2 on t.cbr=t2.bgqcbr
group by t.cbr

[CHQIUU]

引用 8 楼 gogodiy 的回复:

CREATE TABLE t1
(
	number INT,
	cbr VARCHAR(10),
	xmmc VARCHAR(10)
)
INSERT INTO t1
SELECT 1,'张三','项目1' UNION ALL
SELECT 2,'李四','项目2' UNION ALL 
SELECT 3,'王武','项目3' UNION ALL
SELECT 4,'王启','项目4'
CREATE TABLE t2
(
	number INT,
	bgqcbr VARCHAR(10),	--变更前
	bghcbr VARCHAR(10),	--变更后
)
INSERT INTO t2
SELECT 1,'王武','李四' UNION ALL
SELECT 1,'李四','张三' UNION ALL
SELECT 2,'王武','李四' UNION ALL
SELECT 3,'张三','王武'
SELECT * FROM t1
SELECT * FROM t2

;WITH AAA AS
(
	SELECT	bgqcbr,
			COUNT(bgqcbr) AS total1
	FROM	t2
	GROUP BY bgqcbr
)

SELECT	A.cbr AS [承办人],
		ISNULL(D.total3,0) AS [承办项目数],
		ISNULL(C.total2,0) AS [调进数],
		ISNULL(B.total1,0) AS [调出数]
FROM	t1 AS A WITH(NOLOCK) LEFT JOIN
		(
			SELECT	bgqcbr,
					COUNT(bgqcbr) AS total1
			FROM	t2
			GROUP BY bgqcbr
		) AS B ON A.cbr=B.bgqcbr LEFT JOIN
		(
			SELECT	bghcbr,
					COUNT(bghcbr) AS total2
			FROM	t2
			GROUP BY bghcbr
		) AS C ON A.cbr=C.bghcbr LEFT JOIN
		(
			SELECT	cbr,
					COUNT(xmmc) AS total3
			FROM	t1
			GROUP BY cbr
		) AS D ON A.cbr=D.cbr

承办人 承办项目数 调进数 调出数 张三 1 1 1 李四 1 2 1 王武 1 1 2 王启 1 0 0

引用 8 楼 gogodiy 的回复:

CREATE TABLE t1
(
	number INT,
	cbr VARCHAR(10),
	xmmc VARCHAR(10)
)
INSERT INTO t1
SELECT 1,'张三','项目1' UNION ALL
SELECT 2,'李四','项目2' UNION ALL 
SELECT 3,'王武','项目3' UNION ALL
SELECT 4,'王启','项目4'
CREATE TABLE t2
(
	number INT,
	bgqcbr VARCHAR(10),	--变更前
	bghcbr VARCHAR(10),	--变更后
)
INSERT INTO t2
SELECT 1,'王武','李四' UNION ALL
SELECT 1,'李四','张三' UNION ALL
SELECT 2,'王武','李四' UNION ALL
SELECT 3,'张三','王武'
SELECT * FROM t1
SELECT * FROM t2

;WITH AAA AS
(
	SELECT	bgqcbr,
			COUNT(bgqcbr) AS total1
	FROM	t2
	GROUP BY bgqcbr
)

SELECT	A.cbr AS [承办人],
		ISNULL(D.total3,0) AS [承办项目数],
		ISNULL(C.total2,0) AS [调进数],
		ISNULL(B.total1,0) AS [调出数]
FROM	t1 AS A WITH(NOLOCK) LEFT JOIN
		(
			SELECT	bgqcbr,
					COUNT(bgqcbr) AS total1
			FROM	t2
			GROUP BY bgqcbr
		) AS B ON A.cbr=B.bgqcbr LEFT JOIN
		(
			SELECT	bghcbr,
					COUNT(bghcbr) AS total2
			FROM	t2
			GROUP BY bghcbr
		) AS C ON A.cbr=C.bghcbr LEFT JOIN
		(
			SELECT	cbr,
					COUNT(xmmc) AS total3
			FROM	t1
			GROUP BY cbr
		) AS D ON A.cbr=D.cbr

承办人 承办项目数 调进数 调出数 张三 1 1 1 李四 1 2 1 王武 1 1 2 王启 1 0 0

这种方法虽然能够统计出来，但是当出现一个人同时承办多个项目时就会出现重复现象，不过这是个很好的处理方法 ，稍加处理就可以正常统计出来了