记一次复杂sql涉及到的sql语法

这是一个报表的sql查询,查询销售员的业绩分析.

一、表说明

customer：客户表，客户与销售人员（通过 salesman_id）关联。
sale_contract :销售合同表
user：用户表(销售人员表)，包含字段如 real_name（姓名）、post_id（职位ID）、dept_id（部门ID）。
post：销售人员的职位信息。
dept：销售人员所属的部门。
region：区域（省份）信息。
customer_settlement 和 customer_settlement_detail：包含结算明细数据，用于计算销售业绩和其他财务指标。

二、函数介绍

GROUP_CONCAT(): 是 MySQL 提供的一个聚合函数，它用于将多个行中的值连接成一个单一的字符串。连接的值可以由指定的分隔符分隔，默认情况下是逗号（,）。适用场景:比如每个客户可能有多个销售员,那么联合查询后,对客户进行分组,就能得到该客户的所有销售员
FORMAT(): 是一个用于将数值格式化为具有特定小数位数的字符串函数。并且会自动转为千分位表示. FORMAT(value, 2) 将数字 value 格式化为一个字符串，保留小数点后两位。即便 value 是整数，返回的结果也会包含小数点后两位（例如 10000 会格式化为 10,000.00）
IFNULL(): 是 MySQL 中的一个条件函数，它用于检查一个表达式是否为 NULL，如果是 NULL，则返回第二个参数的值。否则，返回第一个参数的值。在一些需要计算的时候将NULL改为0比较方便.
COALESCE():是一个 SQL 函数，它接受多个参数，返回第一个非 NULL 的值。如果所有参数都是 NULL，则返回 NULL。如果 no_complete_settle_amount 为 NULL，则 COALESCE(noCompleteSettle.no_complete_settle_amount, 0) 会返回 0
CONCAT() 是 MySQL 中的一个字符串连接函数，用于将多个字符串连接成一个新的字符串。
SUM():是 SQL 中的一个聚合函数，用于计算一列数字的总和。在这个查询中，它用于计算所有发货金额的总和。
COUNT(): 是一个聚合函数，用于计算某列或某个表的记录数。这里，COUNT(*) 用于计算与每个销售人员相关联的客户数（即每个销售人员负责的客户的总数）。
FIND_IN_SET(): 是 MySQL 中的一个字符串函数，用于查找一个字符串在一个由逗号分隔的字符串列表中的位置。如果该字符串在列表中存在，FIND_IN_SET() 返回其位置（从 1 开始）；如果该字符串不在列表中，则返回 0。如果提供的字符串为 NULL，则返回 NULL。
if(): IF 函数用于根据条件判断返回两个值中的一个。用法:IF(条件, 条件成立时的值, 条件不成立时的值)

三、sql片段分析

3.1 区域子查询

该子查询相对简单,不过因为查询的区域是销售员负责的客户的地区,所以当一个销售员对应多个客户,区域就可能是多个值,所以使用了GROUP_CONCAT进行拼接,使用DISTINCT去重

LEFT JOIN (
    SELECT uu.id,GROUP_CONCAT(DISTINCT ur.province_name) province_name
    FROM  customer u
    LEFT JOIN user uu ON FIND_IN_SET(uu.id,u.salesman_id)
    LEFT JOIN region ur ON u.province_code = ur.province_code AND ur.region_level  = 1
    GROUP BY uu.id
) area ON area.id = uu.id

3.2 客户数子查询

这个子查询也不难,是为了查询每个销售员在指定时间范围,负责多少个有效客户.思路是查询当前销售员签订了多少有效或失效(当前失效,合同有效区与查询时间区间有重叠)合同,然后对合同对应的客户进行去重求个数

LEFT JOIN (
  SELECT uu.id,COUNT(DISTINCT usc.customer_id) count
  FROM  sale_contract  usc
  LEFT JOIN user uu ON FIND_IN_SET(uu.id,usc.signer)
  WHERE usc.is_deleted  = 0 AND usc.contract_status IN (4, 5)
  <if test="q.startDate != null and q.startDate != '' and q.endDate != null and q.endDate != ''">
	and usc.sign_date <![CDATA[ < ]]> #{q.endDate}
	and usc.validity_date <![CDATA[ > ]]>  #{q.startDate}
  </if>
  GROUP BY uu.id
) sale ON sale.id = uu.id

3.3 销售目标子查询

这个子查询关联销售计划,对计划数值进行累加

LEFT JOIN (
  SELECT c.salesman_id,SUM(c.sales_target) sales_target
  FROM sale_plan a
  LEFT JOIN usale_plan_detail b on b.sale_plan_id = a.id
  LEFT JOIN sale_plan_detail_split c on c.sale_plan_detail_id = b.id
  WHERE a.is_deleted  = 0 AND a.execute_status = 1
  <if test="q.startDate != null and q.startDate != '' and q.endDate != null and q.endDate != ''">
    and a.start_plan_date <![CDATA[ <= ]]> #{query.endDate}
    and a.end_plan_date <![CDATA[ >= ]]> #{query.startDate}
  </if>
  GROUP BY c.salesman_id
) target ON target.salesman_id = uu.id

3.4 结算子查询

该子查询查看账单时间在目标区间内的发货,退货与回款
settlement_type: (1-回款;2-发货;3-退货;4-调出;5-调入;
deliverNum 发货量=发货-调出+调入;发货金额也是类似的;

LEFT JOIN (
  SELECT uu.id,
    sum(if(csd.settlement_type=2,csd.prod_weight,0))  -  sum(if(csd.settlement_type=4,csd.prod_weight,0))  + sum(if(csd.settlement_type=5,csd.prod_weight,0)) deliverNum ,
    sum(if(csd.settlement_type=2,csd.prod_sub_total,0))  -  sum(if(csd.settlement_type=4,csd.prod_sub_total,0))  + sum(if(csd.settlement_type=5,csd.prod_sub_total,0)) deliverMoney ,
    sum(if(csd.settlement_type=3,csd.prod_weight,0)) returnNum,
    sum(if(csd.settlement_type=3,csd.prod_sub_total,0)) returnMoney,
    sum(if(csd.settlement_type=1,csd.received_sub_total_amount,0)) receipt
  FROM  customer_settlement_detail  csd
  LEFT JOIN user uu ON FIND_IN_SET(uu.id,csd.sale_men_id)
  WHERE csd.is_deleted  = 0
  <if test="query.startDate != null and query.startDate != '' and query.endDate != null and query.endDate != ''">
	and csd.detail_date <![CDATA[ <= ]]> #{query.endDate} 
       and csd.detail_date <![CDATA[ >= ]]> #{query.startDate}
  </if>
  GROUP BY uu.id
) settle ON settle.id = uu.id

3.5 已结算单子查询

该子查询是为了查询销售员的期初余额。期初余额指的是时间区间的前一天的余额。
customer_settlement 表存的是待结算与已结算单据，待结算单据还未计算奖励，可以使用详情表与未结算的一起，所以已结算单独拿出来进行计算。
计算逻辑是本单业务员是谁算谁的，同时每一结算单有奖励，奖励按照本单业务员的业绩进行按比例分配。
所以先按照单据和业务员查询每一单每一个业务员的业绩。本子查询下面的子查询得到的是订单，销售员，当前单销售员业绩，当前单总业绩。

当前单当前销售员业绩查询：
settlement_type: (1-回款;2-发货;3-退货;4-调出;5-调入;

SUM(
    CASE
          WHEN detail.settlement_type = 1 THEN +received_sub_total_amount
          WHEN detail.settlement_type IN(2,5) THEN -prod_sub_total
          WHEN detail.settlement_type IN(3,4) THEN +prod_sub_total
          ELSE 0
    END
)

当前单据总业绩：当前期减去上一期再减去本期奖励
通过当前业务员业绩除以本单总业绩，再乘以奖励得到本单业务员奖励

LEFT JOIN (
    SELECT salemanId, sum(salemanPerformance) saleTotalPerformance
    FROM(
        SELECT
            settlement.id as settlementId,
            detail.sale_men_id as salemanId,
            (
               SUM(
                   CASE
                        WHEN detail.settlement_type = 1 THEN +received_sub_total_amount
                        WHEN detail.settlement_type IN(2,5) THEN -prod_sub_total
                        WHEN detail.settlement_type IN(3,4) THEN +prod_sub_total
                        ELSE 0
                   END
                ) / (current_period_amount - last_period_amount - discount_amount) * discount_amount
                +
                SUM(
                    CASE
                        WHEN detail.settlement_type = 1 THEN +received_sub_total_amount
                        WHEN detail.settlement_type IN(2,5) THEN -prod_sub_total
                        WHEN detail.settlement_type IN(3,4) THEN +prod_sub_total
                        ELSE 0
                    END
                )
            ) as salemanPerformance
        FROM customer_settlement settlement
        LEFT JOIN customer_settlement_detail detail
        ON settlement.id = detail.settlement_id
        WHERE detail.is_settle = 1 AND date_format(settlement_date,'%Y-%m-%d') <![CDATA[ <= ]]> date_format(#{query.yestodayDate},'%Y-%m-%d')
        GROUP BY settlement.id, detail.sale_men_id
    ) salemanPerformance
    GROUP BY salemanId
) completeSettle on completeSettle.salemanId = uu.id

3.6 未结算订单

该子查询比较简单，就是在已结算基础上，去除奖励的计算，这里就不再列举了。

3.7 预收款子查询

该子查询与区域子查询差不多，都是简单的双表关联。然后对预收款进行简单的SUM运算。

LEFT JOIN (
    SELECT uu.id,SUM(uro.received_amount) received_amount
    FROM  received_order  uro
    LEFT JOIN user uu ON FIND_IN_SET(uu.id,uro.salesman_id)
    WHERE uro.is_deleted  = 0 AND uro.pay_collection_type = 0 AND uro.received_status = 1
    <if test="q.startDate != null and q.startDate != '' and q.endDate != null and q.endDate != ''">
        and uro.received_date <![CDATA[ <= ]]> #{q.endDate}
        and uro.received_date <![CDATA[ >= ]]> #{q.startDate}
    </if>
    GROUP BY uu.id
) receive ON receive.id = uu.id

四、SQL

经过表的介绍，涉及到的函数的介绍，以及对于每一个子查询进行分析之后，这个看起来比较复杂的sql也相对没有那么难以理解了。将每一个子查询看作一个表，也就是一个以销售员为基础的的表，通过销售员ID进行关联销售员的区域，客户数，业绩，发货退货等信息。
当然，有一些放在内存中实现可能更容易理解与调试。这里只是为了通过这个熟悉一些sql的语法，函数等

SELECT uu.id,uu.real_name saleman,up.post_name post,ud.dept_name dept,area.province_name region,IFNULL(sale.count,0) customerNum,
        FORMAT(IFNULL(target.sales_target,0),2) salesTarget,
        FORMAT(IFNULL(settle.deliverNum,0),2) deliverNum,
        FORMAT(IFNULL(settle.deliverMoney,0),2) deliverMoney,
        FORMAT(IFNULL(settle.returnNum,0),2) returnNum,
        FORMAT(IFNULL(settle.returnMoney,0),2) returnMoney,IFNULL(settle.receipt,0) receipt,
        FORMAT(IFNULL(completeSettle.saleTotalPerformance,0) + IFNULL(noCompleteSettle.amount,0),2) AS startBalance,
        FORMAT(IFNULL(completeSettle.saleTotalPerformance,0) + IFNULL(noCompleteSettle.amount,0) + IFNULL(settle.receipt,0) - IFNULL(settle.deliverMoney,0) + IFNULL(settle.returnMoney,0),2) as preReceiptBalance,
        FORMAT(IFNULL(receive.received_amount,0),2) received_amount,
        FORMAT(IFNULL(settle.deliverNum,0) - IFNULL(settle.returnNum,0),2) completeNum
        FROM customer uc
        LEFT JOIN user uu ON FIND_IN_SET(uu.id,uc.salesman_id)
        LEFT JOIN post up ON uu.post_id = up.id
        LEFT JOIN dept ud ON uu.dept_id = ud.id
        LEFT JOIN (
            SELECT uu.id,GROUP_CONCAT(DISTINCT ur.province_name) province_name
            FROM  customer u
            LEFT JOIN user uu ON FIND_IN_SET(uu.id,u.salesman_id)
            LEFT JOIN region ur ON u.province_code = ur.province_code AND ur.region_level  = 1
            GROUP BY uu.id
        ) area ON area.id = uu.id

        LEFT JOIN (
            SELECT uu.id,COUNT(DISTINCT usc.customer_id) count
            FROM  sale_contract  usc
            LEFT JOIN user uu ON FIND_IN_SET(uu.id,usc.signer)
            WHERE usc.is_deleted  = 0 AND usc.contract_status IN (4, 5)
            <if test="query.startDate != null and query.startDate != '' and query.endDate != null and query.endDate != ''">
                and usc.sign_date <![CDATA[ < ]]> #{query.endDate}
                and usc.validity_date <![CDATA[ > ]]>  #{query.startDate}
            </if>
            GROUP BY uu.id
        ) sale ON sale.id = uu.id


        LEFT JOIN (
            SELECT c.salesman_id,SUM(c.sales_target) sales_target
            FROM sale_plan a
            LEFT JOIN sale_plan_detail b on b.sale_plan_id = a.id
            LEFT JOIN sale_plan_detail_split c on c.sale_plan_detail_id = b.id
            WHERE a.is_deleted  = 0 AND a.execute_status = 1
            <if test="query.startDate != null and query.startDate != '' and query.endDate != null and query.endDate != ''">
                and a.start_plan_date <![CDATA[ <= ]]> #{query.endDate}
                and a.end_plan_date <![CDATA[ >= ]]> #{query.startDate}
            </if>
            GROUP BY c.salesman_id
        ) target ON target.salesman_id = uu.id


        LEFT JOIN (
            SELECT uu.id,
                sum(if(csd.settlement_type=2,csd.prod_weight,0))  -  sum(if(csd.settlement_type=4,csd.prod_weight,0))  + sum(if(csd.settlement_type=5,csd.prod_weight,0)) deliverNum ,
                sum(if(csd.settlement_type=2,csd.prod_sub_total,0))  -  sum(if(csd.settlement_type=4,csd.prod_sub_total,0))  + sum(if(csd.settlement_type=5,csd.prod_sub_total,0)) deliverMoney ,
                sum(if(csd.settlement_type=3,csd.prod_weight,0)) returnNum,
                sum(if(csd.settlement_type=3,csd.prod_sub_total,0)) returnMoney,
                sum(if(csd.settlement_type=1,csd.received_sub_total_amount,0)) receipt
            FROM  customer_settlement_detail  csd
            LEFT JOIN user uu ON FIND_IN_SET(uu.id,csd.sale_men_id)
            WHERE csd.is_deleted  = 0
            <if test="query.startDate != null and query.startDate != '' and query.endDate != null and query.endDate != ''">
                and csd.detail_date <![CDATA[ <= ]]> #{query.endDate}
                and csd.detail_date <![CDATA[ >= ]]> #{query.startDate}
            </if>
            GROUP BY uu.id
        ) settle ON settle.id = uu.id

        LEFT JOIN (
        SELECT salemanId, sum(salemanPerformance) saleTotalPerformance
        FROM(
            SELECT
                settlement.id as settlementId,
                detail.sale_men_id as salemanId,
                (
                    SUM(
                        CASE
                            WHEN detail.settlement_type = 1 THEN +received_sub_total_amount
                            WHEN detail.settlement_type IN(2,5) THEN -prod_sub_total
                            WHEN detail.settlement_type IN(3,4) THEN +prod_sub_total
                            ELSE 0
                        END
                ) / (current_period_amount - last_period_amount - discount_amount) * discount_amount
                +
                SUM(
                    CASE
                        WHEN detail.settlement_type = 1 THEN +received_sub_total_amount
                        WHEN detail.settlement_type IN(2,5) THEN -prod_sub_total
                        WHEN detail.settlement_type IN(3,4) THEN +prod_sub_total
                        ELSE 0
                    END
                )
             ) as salemanPerformance
            FROM customer_settlement settlement
            LEFT JOIN customer_settlement_detail detail
            ON settlement.id = detail.settlement_id
            WHERE detail.is_settle = 1 AND date_format(settlement_date,'%Y-%m-%d') <![CDATA[ <= ]]> date_format(#{query.yestodayDate},'%Y-%m-%d')
            GROUP BY settlement.id, detail.sale_men_id
        ) salemanPerformance
        GROUP BY salemanId
        ) completeSettle on completeSettle.salemanId = uu.id

        LEFT JOIN (
        SELECT
            sale_men_id as salemanId,
            SUM(
                CASE
                    WHEN detail.settlement_type = 1 THEN +received_sub_total_amount
                    WHEN detail.settlement_type IN(2,5) THEN -prod_sub_total
                    WHEN detail.settlement_type IN(3,4) THEN +prod_sub_total
                    ELSE 0
                END
            ) as amount
        FROM customer_settlement_detail detail
        WHERE is_settle = 0 AND date_format(detail_date,'%Y-%m-%d') <![CDATA[ <= ]]> date_format(#{query.yestodayDate},'%Y-%m-%d')
        GROUP BY sale_men_id
        ) noCompleteSettle on noCompleteSettle.salemanId = uu.id

        LEFT JOIN (
        SELECT uu.id,SUM(uro.received_amount) received_amount
        FROM  received_order  uro
        LEFT JOIN user uu ON FIND_IN_SET(uu.id,uro.salesman_id)
        WHERE uro.is_deleted  = 0 AND uro.pay_collection_type = 0 AND uro.received_status = 1
        <if test="query.startDate != null and query.startDate != '' and query.endDate != null and query.endDate != ''">
            and uro.received_date <![CDATA[ <= ]]> #{query.endDate}
            and uro.received_date <![CDATA[ >= ]]> #{query.startDate}
        </if>
        GROUP BY uu.id
        ) receive ON receive.id = uu.id

        <where>
            <if test="query.deptQuery !=null and query.deptQuery != ''">
                ud.dept_name like concat('%',#{query.deptQuery},'%')
            </if>
            <if test="query.regionQuery !=null and query.regionQuery != ''">
                and area.province_name like concat('%',#{query.regionQuery},'%')
            </if>
            <if test="query.postQuery !=null and query.postQuery != ''">
                and up.post_name like concat('%',#{query.postQuery},'%')
            </if>
            <if test="query.salemanQuery !=null and query.salemanQuery != ''">
                and uu.real_name like concat('%',#{query.salemanQuery},'%')
            </if>
        </where>

        GROUP BY uu.id,uu.real_name,up.post_name,ud.dept_name,area.province_name,sale.count,target.sales_target,settle.deliverNum,settle.deliverMoney,settle.returnNum,settle.returnMoney,
        settle.receipt,receive.received_amount,completeSettle.saleTotalPerformance,noCompleteSettle.amount
        ORDER BY completeNum  DESC

欢迎大家留言，以便于后面的人更快解决问题！另外亦欢迎大家可以关注我的微信公众号，方便利用零碎时间互相交流。共勉！

posted @ 2024-11-23 00:31 东方欲晓_莫道君行早阅读(5) 评论(0) 编辑收藏举报

刷新页面返回顶部

东方欲晓_莫道君行早

莫道君行早，更有早行人