一个多表联合查询的问题。

[XinJW]

有一表是员工资料表Employee里面存放数据如下列。
Employee_Name,Nick_Name
王大帅 帅帅
李燕 KC
还有顾客表Custom
CustomNO CustomName ORDERDATE SALES
2222222 李三 2013-4-29 帅帅
1111111 王一 2013-5-1 王大帅
1111112 王二 2013-5-1 KC
还有付款表MONEY
CustomNO Money Date
1111111 1000 2013-5-1
2222222 500 2013-5-2
1111112 2000 2013-5-2

在CUSTOM表中的SALES里面的帅帅和王大帅是一个人，所以业绩也是一个人的。应该得出的结果是2013年5月份业绩
王大帅 1500
李燕 2000

这个用一个SQL怎么得出。谢谢。

[chuifengde]

SELECT a.Employee_Name,SUM([Money]) HJ
FROM Employee a INNER JOIN Custom b ON CHARINDEX(b.SALES+',', Employee_Name+','+Nick_Name+',')>0 INNER JOIN [Money] c ON b.CustomNO=c.CustomNO
GROUP BY a.Employee_Name

[daiyueqiang2045]

SELECT a.Employee_Name,SUM([Money]) HJ
FROM Employee a INNER JOIN Custom b ON a.Employee_Name=b.SALES or a.Nick_Name=b.SALES 
INNER JOIN [Money] c ON b.CustomNO=c.CustomNO
GROUP BY a.Employee_Name

估计这个有问题，为什么Employee没有id字段呢？

[XinJW]

引用 1 楼 chuifengde 的回复:

SELECT a.Employee_Name,SUM([Money]) HJ
FROM Employee a INNER JOIN Custom b ON CHARINDEX(b.SALES+',', Employee_Name+','+Nick_Name+',')>0 INNER JOIN [Money] c ON b.CustomNO=c.CustomNO
GROUP BY a.Employee_Name

正解如果加条件，Employee_name="李燕" 付款时间是2013年5月份，怎么加。谢谢。

[chuifengde]

....
where Employee_name="李燕" and datediff(m,[date],'2013-5-1')=0
group by ...

[XinJW]

引用 4 楼 chuifengde 的回复:

....
where Employee_name="李燕" and datediff(m,[date],'2013-5-1')=0
group by ...

服务器: 消息 207，级别 16，状态 3，行 1 列名 '李燕'无效。

[MrYangkang]

select a.Employee_Name,sum(b.money)as money
from
(
select Employee_Name,customno from Custom,Employee where sales =Employee_Name or sales=Nick_Name
)a inner join
(
select * from MONEY
)b on a.customno = b.customno
group by a.Employee_Name

看结果