销售——合同——提成

一、表

注：数据库：MYsql；tbl_ConList表中的cConNumber列的类型已改为date类型，否则无法与指定日期比较，如：A.cConDate<='2006-12-31'

二、查询

1.得到所有销售人员对应合同信息和提成金额

SQL：

select A.cConNumber,B.cSaleName,SUM(C.payMoney)
from tbl_ConList A
inner join tbl_Sale B inner join tbl_PayMoney C  where A.cSaleID=B.cID and  A.cConID=C.cConID
group by A.cConNumber,B.cSaleName

结果：

2.得到合同日期在“2006-12-31”之前，每个销售人员的合同总额和提成总额：

SQL:

select A.cConNumber,B.cSaleName,SUM(C.payMoney)
from tbl_ConList A
inner join tbl_Sale B inner join tbl_PayMoney C  where A.cSaleID=B.cID and  A.cConID=C.cConID
and A.cConDate<='2006-12-31'
group by A.cConNumber,B.cSaleName

结果：

3.得到没有签过合同的销售人员信息：

SQL:

SELECT B.cSaleName
FROM tbl_ConList A
right JOIN tbl_Sale B ON (A.cSaleID=B.cID)
where A.cSaleID is null

或者

SQL：

SELECT A.cSaleName
FROM tbl_Sale  A
left JOIN tbl_ConList B ON (A.cID=B.cSaleID)
where B.cSaleID is null

省略ON后的()也可以：

SQL：

SELECT A.cSaleName
FROM tbl_Sale  A
left JOIN tbl_ConList B on A.cID=B.cSaleID
where B.cSaleID is null

结果：

注意：使用左外连接（left join）或右外连接（right join），联结字段的条件为on，不能用where，如：

SQL：

SELECT A.cSaleName
FROM tbl_Sale  A
left JOIN tbl_ConList B where A.cID=B.cSaleID
and B.cSaleID is null

这样在语法上是错误的，应把where改为on.

但是如果是inner join 就没有这个限制，如：

SQL：

SELECT A.cSaleName
FROM tbl_Sale  A
inner JOIN tbl_ConList B on A.cID=B.cSaleID

或

SQL：

SELECT A.cSaleName
FROM tbl_Sale  A
inner JOIN tbl_ConList B where A.cID=B.cSaleID

都可以查出结果。

4.得到某年每位销售人员每季度签订的合同数量:

SQL:

select t.cSaleName,count(a.cConID) from tbl_Sale t INNER JOIN
(select A.cID,A.cSaleName,B.cConID from tbl_Sale A inner join tbl_ConList B on(A.cID=B.cSaleID)
where (Year(B.cConDate)=2006 and Month(B.cConDate)=1)
OR (Year(B.cConDate)=2006 and Month(B.cConDate)=2)
OR (Year(B.cConDate)=2006 and Month(B.cConDate)=3))a on (t.cID=a.cID)
group by t.cSaleName

结果：

改变条件 (4,5,6) (7,8,9) (10,11,12)

上面的SQL可以简化，即直接从查询结果a中查询（无需查询表tbl_Sale再INNER JOIN 结果表a，同时去掉了结果表a中的A.cID）

SQL：

select a.cSaleName,count(a.cConID) from 
(select A.cSaleName,B.cConID from tbl_Sale A inner join tbl_ConList B on(A.cID=B.cSaleID)
where (Year(B.cConDate)=2006 and Month(B.cConDate)=1)
OR (Year(B.cConDate)=2006 and Month(B.cConDate)=2)
OR (Year(B.cConDate)=2006 and Month(B.cConDate)=3))a
group by a.cSaleName

还可以简化，去掉嵌套

SQL：

select A.cSaleName,count(B.cConID) from tbl_Sale A inner join tbl_ConList B on(A.cID=B.cSaleID)
where (Year(B.cConDate)=2006 and Month(B.cConDate)=1)
OR (Year(B.cConDate)=2006 and Month(B.cConDate)=2)
OR (Year(B.cConDate)=2006 and Month(B.cConDate)=3)
group by A.cSaleName

 注意：聚合函数与group by的配合使用，以上SQL的运行逻辑是先对cSaleName进行分组，然后对重复行进行统计。

 

小结：不管是左外连接、右外连接还是内连接，联结条件均使用on，以免弄错。