多表查询，分组合计的问题

[layxbjl]

表1 Employee
id name
1 张三
2 李四
3 王五
4 赵六
表2 GoodsSell 销售单
id Num CreatedBy
1 X-1 张三
2 X-2 张三
3 X-3 张三
4 X-4 王五
5 X-5 李四
6 X-6 李四

表3 GoodsBuy 采购单
id Num CreatedBy
1 B-1 张三
2 B-2 张三
3 B-3 张三
4 B-4 李四
5 B-5 李四
6 B-6 赵六

sql代码：

    SQL = "select Employee.Name as 员工姓名,count(GoodsSell.Num) as 销售单数量,count(GoodsBuy.Num) as 采购单数量 from Employee left join GoodsSell on Employee.Name=GoodsSell.CreatedBY and CONVERT(varchar(12),GoodsSell.createddate,111) between '" & dtpFromDate.Value & "' and '" & dtpToDate.Value & "' left join Goodsbuy on Employee.Name=Goodsbuy.CreatedBY and CONVERT(varchar(12),Goodsbuy.createddate,111) between '" & dtpFromDate.Value & "' and '" & dtpToDate.Value & "' where Employee.department<>'离职' Group by Employee.Name order by Employee.Name "

想得到的结果
员工姓名 销售单数量 采购单数量
张三 3 3
李四 2 2
王五 1 0
赵六 0 1


实际运行结果
员工姓名 销售单数量 采购单数量
张三 9 9
李四 4 4
王五 1 0
赵六 0 1


只要是销售和采购这边都有记录的，就会把两边的合计数量一乘然后分别赋给两边，如果只有一边有的则没有问题,如果只查两个表则都正常，如下：sql代码：

    SQL = "select Employee.Name as 员工姓名,count(GoodsSell.Num) as 销售单数量 from Employee left join GoodsSell on Employee.Name=GoodsSell.CreatedBY and CONVERT(varchar(12),GoodsSell.createddate,111) between '" & dtpFromDate.Value & "' and '" & dtpToDate.Value & "' where Employee.department<>'离职' Group by Employee.Name order by Employee.Name "

请问哪里有问题，目标是6个表以上的查询。谢谢

[layxbjl]

Goodsbuy.createddate,111) between '" & dtpFromDate.Value & "' and '" & dtpToDate.Value & "' 这个是日期条件，我这里没列，不影响结果

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-14 16:44:01
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[Employee]
if object_id('[Employee]') is not null drop table [Employee]
go 
create table [Employee]([id] int,[name] varchar(4))
insert [Employee]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六'
if object_id('[GoodsSell]') is not null drop table [GoodsSell]
go 
create table [GoodsSell]([id] int,[Num] varchar(3),[CreatedBy] varchar(4))
insert [GoodsSell]
select 1,'X-1','张三' union all
select 2,'X-2','张三' union all
select 3,'X-3','张三' union all
select 4,'X-4','王五' union all
select 5,'X-5','李四' union all
select 6,'X-6','李四'
if object_id('[GoodsBuy]') is not null drop table [GoodsBuy]
go 
create table [GoodsBuy]([id] int,[Num] varchar(3),[CreatedBy] varchar(4))
insert [GoodsBuy]
select 1,'B-1','张三' union all
select 2,'B-2','张三' union all
select 3,'B-3','张三' union all
select 4,'B-4','李四' union all
select 5,'B-5','李四' union all
select 6,'B-6','赵六'
--------------开始查询--------------------------

--select * from [GoodsBuy]

--select * from [GoodsSell]

select a.NAME 员工姓名  ,COUNT(DISTINCT b.num) 销售单数量,COUNT(DISTINCT c.num)采购单数量
from [Employee] a LEFT JOIN [GoodsBuy] b ON a.NAME=b.createdby 
LEFT JOIN [GoodsSell] c ON a.NAME=c.createdby
GROUP BY a.NAME

----------------结果----------------------------
/* 
员工姓名 销售单数量       采购单数量
---- ----------- -----------
李四   2           2
王五   0           1
张三   3           3
赵六   1           0
*/

[發糞塗牆]

别名缓一下

select a.NAME 员工姓名  ,COUNT(DISTINCT b.num) 采购单数量,COUNT(DISTINCT c.num)销售单数量
from [Employee] a LEFT JOIN [GoodsBuy] b ON a.NAME=b.createdby 
LEFT JOIN [GoodsSell] c ON a.NAME=c.createdby
GROUP BY a.NAME

[--小F--]

SELECT
   A.NAME,
   ISNULL(B.NUM,0) AS 销售单数量,
   ISNULL(C.NUM,0) AS 采购单数量
FROM
   Employee AS A
LEFT JOIN
   (SELECT CreatedBy,COUNT(1) AS NUM FROM GoodsSell GROUP BY CreatedBy) AS B
ON
    A.NAME=B.CreatedBy
LEFT JOIN
   (SELECT CreatedBy,COUNT(1) AS NUM FROM  GoodsBuy GROUP BY CreatedBy) AS C
ON
    A.NAME=C.CreatedBy

试试这个

[layxbjl]

非常感觉，就是加了一个DISTINCT就好了，原因为何？

[發糞塗牆]

关联的时候重复了

[layxbjl]

以我的理解就算要加也是加在a.name这里

[發糞塗牆]

因为我group by了，所以也去重了

[layxbjl]

难理解 ，谢谢

[發糞塗牆]

group by也可以去重的

[layxbjl]

那再请问一下，我是想对6个表以上进行查询，是否还有更好的方法