sql

2024-05-20

sample
sql

使用过的sql语句，记录下来，不再重复写

mysql

elt

充值金额统计
SELECT 
    ELT(
        INTERVAL(a.total_recharge_amount,0, 500000, 1000000, 2000000, 3000000), 
        '0-五千', '五千-一万', '一万-两万', '两万-三万', '三万以上'
    ) AS user_level,
    COUNT(a.user_id) AS cnt
FROM (
    SELECT user_id, SUM(recharge_amount) AS total_recharge_amount
    FROM recharge_order
    WHERE brand_id=100 AND status = 1 AND pay_status =1 AND archive_time >= CURDATE() - INTERVAL 1 YEAR
    GROUP BY user_id
) a 
GROUP BY user_level;

消费金额统计
SELECT 
    ELT(
        INTERVAL(a.balance,0, 500000, 1000000, 2000000, 3000000), 
        '0-五千', '五千-一万', '一万-两万', '两万-三万', '三万以上'
    ) AS user_level,
    COUNT(a.user_id) AS cnt
FROM (
    SELECT user_id, SUM(card_pay_amount ) as balance
    FROM card_pay_order
    WHERE brand_id=100  AND `status` =1 AND archive_time >= CURDATE() - INTERVAL 1 YEAR
    GROUP BY user_id
) a 
GROUP BY user_level;

UPDATE user
SET 
    year = CASE 
               WHEN birthday != '' THEN CAST(SUBSTRING(birthday, 1, 4) AS UNSIGNED)
               ELSE 0 
           END,
    month = CASE 
                WHEN birthday != '' THEN CAST(SUBSTRING(birthday, 6, 2) AS UNSIGNED)
                ELSE 0 
            END,
    day = CASE 
              WHEN birthday != '' THEN CAST(SUBSTRING(birthday, 9, 2) AS UNSIGNED)
              ELSE 0 
          END;