22,300
社区成员




USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
IF OBJECT_ID('C') IS NOT NULL DROP TABLE C
CREATE TABLE A([工号] int,[姓名] nvarchar(10))
CREATE TABLE B([名称] nvarchar(10),[销售] NVARCHAR(10),[录入时间] datetime)
CREATE TABLE C([名称] nvarchar(10),[销售] NVARCHAR(10),[需求] NVARCHAR(10),[录入时间] datetime)
SET NOCOUNT ON
INSERT INTO a VALUES (1,'A君')
INSERT INTO a VALUES (2,'B君')
INSERT INTO a VALUES (3,'C君')
--
INSERT INTO b VALUES ('名称1','A君','2017-11-11')
INSERT INTO b VALUES ('名称2','A君','2017-11-11')
INSERT INTO b VALUES ('名称3','B君','2017-11-11')
INSERT INTO b VALUES ('名称4','B君','2017-11-11')
INSERT INTO b VALUES ('名称5','B君','2017-11-11')
INSERT INTO b VALUES ('名称6','A君','2017-11-11')
INSERT INTO b VALUES ('名称7','C君','2017-11-11')
--
INSERT INTO c VALUES ('名称1','A君','产品A','2017-11-11')
INSERT INTO c VALUES ('名称2','A君','产品A','2017-11-11')
INSERT INTO c VALUES ('名称6','C君','产品C','2017-11-11')
INSERT INTO c VALUES ('名称8','C君','产品C','2017-11-11')
SELECT
a.[姓名]
,(SELECT COUNT(1) FROM b WHERE a.[姓名]=b.[销售]) as [B表录入数]
,(SELECT COUNT(1) FROM c WHERE a.[姓名]=c.[销售]) as [C表录入数]
FROM a
/*
姓名 B表录入数 C表录入数
---------- ----------- -----------
A君 3 2
B君 3 0
C君 1 2
*/
select A.销售,isnull(B表录入数,0),isnull(C表需求数,0)
from 表A AS A
left join (select 销售,count(*) as B表录入数 from 表B group by 销售) as B ON A.销售=B.销售
left join (select 销售,count(distinct 需求) as C表需求数 from 表C group by 销售) as C ON A.销售=C.销售