关于java:最近做统计用到的几个常用sql

计算同比

SELECT
    old.now_time,
    ROUND(( new.count - old.count) / old.count * 100, 2 ),
    new.count ncount,
    old.count ocount 
FROM (
SELECT
    sum(IF ( b.count > 1, 1, 1) ) count,
    b.now_time,
    b.tenant_code 
FROM
    (
    SELECT
        date_format(ro.create_dt, '%Y-%m') AS now_time,
        a.tenant_code,
        a.order_no,
        count(a.id) count 
    FROM
        t_rep_order_timeout a 
    INNER JOIN t_rep_order ro on 
        a.tenant_code = ro.tenant_code and a.order_no = ro.order_no
    WHERE
        a.tenant_code = 'zlyy' 
    GROUP BY
        date_format(ro.create_dt, '%Y-%m'),
        a.tenant_code,
        a.order_no 
    ) b 
GROUP BY
    b.now_time,
    b.tenant_code
    ) new 
LEFT JOIN
(SELECT
    sum(IF ( b.count > 1, 1, 1) ) count,
    b.now_time,
    b.tenant_code 
FROM
    (
    SELECT
        date_format(DATE_ADD( ro.create_dt, INTERVAL 1 YEAR), '%Y-%m') AS now_time,
        a.tenant_code,
        a.order_no,
        count(a.id) count 
    FROM
        t_rep_order_timeout a 
    INNER JOIN t_rep_order ro on 
        a.tenant_code = ro.tenant_code and a.order_no = ro.order_no
    WHERE
        a.tenant_code = 'zlyy' 
    GROUP BY
        date_format(DATE_ADD( ro.create_dt, INTERVAL 1 YEAR), '%Y-%m'),
        a.tenant_code,
        a.order_no 
    ) b 
GROUP BY
    b.now_time,
    b.tenant_code) old 
    on old.tenant_code = new.tenant_code and old.now_time = new.now_time

计算环比

SELECT
    old.now_time,
    ROUND(( new.count - old.count) / old.count * 100, 2 ),
    new.count ncount,
    old.count ocount 
FROM (
SELECT
    sum(IF ( b.count > 1, 1, 1) ) count,
    b.now_time,
    b.tenant_code 
FROM
    (
    SELECT
        date_format(ro.create_dt, '%Y-%m') AS now_time,
        a.tenant_code,
        a.order_no,
        count(a.id) count 
    FROM
        t_rep_order_timeout a 
    INNER JOIN t_rep_order ro on 
        a.tenant_code = ro.tenant_code and a.order_no = ro.order_no
    WHERE
        a.tenant_code = 'zlyy' 
    GROUP BY
        date_format(ro.create_dt, '%Y-%m'),
        a.tenant_code,
        a.order_no 
    ) b 
GROUP BY
    b.now_time,
    b.tenant_code
    ) new 
LEFT JOIN
(SELECT
    sum(IF ( b.count > 1, 1, 1) ) count,
    b.now_time,
    b.tenant_code 
FROM
    (
    SELECT
        date_format(DATE_ADD( ro.create_dt, INTERVAL 1 MONTH), '%Y-%m') AS now_time,
        a.tenant_code,
        a.order_no,
        count(a.id) count 
    FROM
        t_rep_order_timeout a 
    INNER JOIN t_rep_order ro on 
        a.tenant_code = ro.tenant_code and a.order_no = ro.order_no
    WHERE
        a.tenant_code = 'zlyy' 
    GROUP BY
        date_format(DATE_ADD( ro.create_dt, INTERVAL 1 MONTH), '%Y-%m'),
        a.tenant_code,
        a.order_no 
    ) b 
GROUP BY
    b.now_time,
    b.tenant_code) old 
    on old.tenant_code = new.tenant_code and old.now_time = new.now_time

获取某一年份所有月份

SELECT
CASE
        
    WHEN
        length(mon) = 1 THEN
            concat('2019-0', mon) ELSE concat('2019-', mon) 
        END months 
FROM
    (SELECT @m := @m + 1 mon FROM t_rep_order_timeout, ( SELECT @m := 0) a ) aa 
    LIMIT 12

此处所用的辅助表 t_rep_oder_timeot 表数据必须超过 12 条

删除表内的反复数据

DELETE t 
FROM
    interview t
    LEFT JOIN (SELECT title, min( id) AS min_id FROM interview GROUP BY title ) t1 ON t.id = t1.min_id 
WHERE
    t1.min_id IS NULL;

欢送搜寻关注自己与敌人共同开发的微信面经小程序【大厂面试助手】和公众号【微瞰技术】