MYSQL,SQL,MYBATIS

• 基础用法

-- sql：
   select t1.* ,
        sum(t2.delivery_num) as deliveryQuality,
        sum(t2.package_num) as deliveryNumber,
        group_concat( DISTINCT t2.des_location) as desWarehouseNames
        from external_deliver_order t1
        left join external_deliver_order_detail t2
        on t1.id = t2.deliver_order_id
        <where>
            <if test="createTime != null">
                date_format(t1.create_time,'%Y-%m-%d') = date_format(#{createTime},'%Y-%m-%d')
            </if>
            <if test="startingPoint != null and startingPoint != ''">
                and t1.departure_place like concat('%',#{startingPoint},'%')
            </if>
            <if test="endTime != null">
                <![CDATA[
                    and date(t1.create_time) <= #{endTime}
                 ]]>
            </if>
            <if test="name != null and name != '' ">
                and ( t1.user_code like concat('%',#{name},'%') or t1.create_user like concat('%',#{name},'%') )
            </if>
            <if test="productName != null and productName != '' ">
                and t2.product_name like concat('%',#{productName},'%')
            </if>
        </where>
        group by t2.deliver_order_id
        order by t1.id desc
        
   --sql2:日期查询
       SELECT
        t1.*
        FROM
        external_purchase_detail t1
        LEFT JOIN external_purchase_plan t2 ON t1.purchase_plan_id = t2.id
        WHERE t1.product_code  = #{productCode} and t1.turn_off_tag = '1'
        and
        <![CDATA[
             DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(t2.create_time)
        ]]>

• 查询JSON数组并解析数组。

截取json串

CREATE DEFINER=`root`@`%` FUNCTION `split_sum`(str VARCHAR(200)) RETURNS int(20)
    DETERMINISTIC
BEGIN
	DECLARE max_size INT(11);
	DECLARE i INT(11);
	DECLARE sum INT(20);
	SET max_size = LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1;
	SET i = 0;
	SET sum = 0;
	WHILE i < max_size DO
		SET i = i + 1;
		SET sum = sum + SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', i),',',-1);
	END	WHILE;
	RETURN sum;
END

截取JSON数组

CREATE DEFINER=`root`@`%` FUNCTION `JSONArraySum`(array JSON) RETURNS double
    DETERMINISTIC
BEGIN
 DECLARE total DOUBLE DEFAULT 0;
 DECLARE idx INT DEFAULT 0;
 DECLARE size INT DEFAULT JSON_LENGTH(array);
 DECLARE temp DOUBLE;
 WHILE idx < size DO
   SET temp :=JSON_EXTRACT(array, CONCAT('$[', idx, ']'));
   SET total := total + temp;
   SET idx := idx + 1;
END WHILE;
RETURN total;
END

将里面的JOSN数组的feeTotal取值相加。

SELECT other_fee,  JSONArraySum(other_fee -> '$[*].feeTotal')  FROM external_deliver_order ORDER BY id DESC;


SELECT
  t1.id,
  t1.trans_code AS '发车单号',
  t1.estimated_freight AS '运费',
	t1.other_fee AS '各项费用项',
	JSONArraySum(t1.other_fee -> '$[*].feeTotal') AS '各项费用值',
  CASE t1.`status`
  WHEN '1' THEN '待发车'
  WHEN '10' THEN '待收货'
  WHEN '99' THEN '部分收货'
  WHEN '100' THEN '全部收货'
  WHEN '101' THEN '作废'
  ELSE '状态不明' END AS '状态(发车单)',
  t1.create_time AS '发车时间',
  t1.departure_place AS '发车地',
  t1.billing_rate AS '增值服务费率(%)',
  t2.added_service_fee as '增值服务费',
  t2.package_spec as '包装规格',
  t2.user_turnover_basket_num AS '周转筐数量',
  t2.expected_arrival_time AS '目的地计划到货时间',
  t2.product_name AS '商品名称',
  t2.modify_cost_price as '修改成本价',
  t2.package_num AS '发货件数',
  t2.delivery_num AS '发货数量',
   round(t2.modify_cost_price * t2.delivery_num,2) AS '基地发货金额',
    CASE t2.`status`
  WHEN '1' THEN '待发车'
  WHEN '10' THEN '待收货'
  WHEN '100' THEN '全部收获'
  WHEN '101' THEN '作废'
  ELSE '状态不明' END AS '状态(发车单明细)',
  t2.price as '基地成本单价(不含运费)',
  ROUND((t2.delivery_amount + IFNULL(t2.apportionment_freight,'0')) / t2.delivery_num ,4)  as '基地成本单价含运费',
  IFNULL(t2.apportionment_freight,0) AS '分摊运费' ,
	t2.other_fee AS '分摊费用各项费用',
	ROUND(JSONArraySum(t2.other_fee -> '$[*].feeTotal'),4) AS '各项费用值',
   ROUND(  (t2.delivery_amount + IFNULL(t2.apportionment_freight,'0') + IFNULL(t2.added_service_fee,'0') ),2 ) AS '基地成本(含运费和增值费)',
  t2.package_spec * t2.receive_num AS '实际收货数量',
  t2.receive_num AS '收货件数',
	t2.receive_time AS '收货时间',
  t3.receive_warehouse_name AS '收货仓',
  t3.receive_warehouse_code as '收货仓编码'
FROM
  external_deliver_order t1
  LEFT JOIN external_deliver_order_detail t2 ON t1.id = t2.deliver_order_id 
  LEFT JOIN external_purchase_detail t3 ON t3.id = t2.purchase_detail_id 
ORDER BY
  t1.id DESC