一、表
注:数据库: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,以免弄错。