sql 承办人变更情况统计问题求助
本帖最后由 QIU176161650 于 2013-04-28 10:58:31 编辑 表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 SQL 统计 变更
[解决办法]
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
[解决办法]
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
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
(
SELECTbgqcbr,
COUNT(bgqcbr) AS total1
FROMt2
GROUP BY bgqcbr
)
SELECTA.cbr AS [承办人],
ISNULL(D.total3,0) AS [承办项目数],
ISNULL(C.total2,0) AS [调进数],
ISNULL(B.total1,0) AS [调出数]
FROMt1 AS A WITH(NOLOCK) LEFT JOIN
(
SELECTbgqcbr,
COUNT(bgqcbr) AS total1
FROMt2
GROUP BY bgqcbr
) AS B ON A.cbr=B.bgqcbr LEFT JOIN
(
SELECTbghcbr,
COUNT(bghcbr) AS total2
FROMt2
GROUP BY bghcbr
) AS C ON A.cbr=C.bghcbr LEFT JOIN
(
SELECTcbr,
COUNT(xmmc) AS total3
FROMt1
GROUP BY cbr
) AS D ON A.cbr=D.cbr