SQL语法记录

coalesce 返回第一个非null的值

需求：计算各个门店的超时订单，若订单存在预约时间，超时已超过预约时间2小时来算；若订单没有预约时间，则以订单创建时间超过2小时来算；

        select a.name as storename,count(b.id) as ordernum
        from store a
                 left join dq_trade_order b on a.id = b.store_id
        where
            # 存在预约时间以预约时间过两小时算,否则以创建时间过两小时来算超时订单
            now() >= coalesce(b.delivery_time, b.create_time) + interval 2 hour
          and b.status in (20, 30)
          and b.create_time between #{timeArray[0]} and #{timeArray[1]}
        group by a.id;

在举例一个 union all 优化的例子：

        select areaCode, sum(price) as totalPrice
        from (
                 # 按送货地址的区域统计销售额
                 select sum(a.pay_price) / 100 as price, b.area as areaCode
                 from dq_trade_order a
                          left join send_address b on a.address_id = b.id
                 where b.area is not null
                   and a.create_time between #{timeArray[0]} and #{timeArray[1]}
                 group by b.area
                 union all
                 # 按门店所在区域统计销售额（如果没有送货地址的区域）
                 select sum(a.pay_price) / 100 as price, c.district as areacode
                 from dq_trade_order a
                          left join send_address b on a.address_id = b.id
                          left join store c on a.store_id = c.id
                 where b.area is null
                   and a.create_time between #{timeArray[0]} and #{timeArray[1]}
                 group by c.district) as combined
        group by areacode;

可以优化为：

# 1.优先按送货地址的区域统计销售额 2.如果没有送货地址的区域,就按照门店所在区域统计销售额
        select coalesce(b.area, c.district) as areaCode,
               sum(a.pay_price) / 100       as totalPrice
        from dq_trade_order a
                 left join send_address b on a.address_id = b.id
                 left join store c on a.store_id = c.id
        where a.create_time between #{timeArray[0]} and #{timeArray[1]}
        group by areaCode;

替换所有表中的所有字段，比如替换数据库中所有表数据的https://bc.gzjkjt.com/minio 为 https://ba.gzjkjt.com:30080/minio

SELECT CONCAT('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME,
              ', ''https://bc.gzjkjt.com/minio'', ''https://ba.gzjkjt.com:30080/minio'') ', ' WHERE ', COLUMN_NAME,
              ' LIKE ''%https://bc.gzjkjt.com/minio%'';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名称';