• 销售——合同——提成


    一、表

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

  • 相关阅读:
    高效 JavaScript
    什么是QName【转】
    gson的简单使用方法
    SWT的FormLayout
    SWT/JFace常用组件容器类
    更改swing应用程序标题栏默认图标
    面试也是自己对自己的面试
    关于Android图片cache处理方法
    【Java】_2_Java程序入门第二课
    【算法和数据结构】_9_线性结构_队列_续_1
  • 原文地址:https://www.cnblogs.com/wql025/p/4942595.html
Copyright © 2020-2023  润新知