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
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
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
(select count(*) from cbrbgqkb where bghcbr=a.cbr) as [调进数],
(select count(*) from cbrbgqkb where bgqcbr=a.cbr) as [调出数]
from xmxxb a
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
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
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