sql时间怎么每天自动切换

Question

a.dt='20220523'
在第二天变为20220524 当天时间-1天如何设置

SELECT
meg_trade_name_2,
sum(CASE WHEN a.dt='20220523' THEN feed_cpc_fy_csm-feed_cpm_fy_csm-ppzx_fc_jp_csm-ppzx_jp_hy_csm ELSE 0 END)/1000 y
FROM apps.apps_v_summary_di a LEFT JOIN apps.apps_v_attack_city_white_list b on a.cust_id=b.cust_id
WHERE attack_city='city'
GROUP BY 1
ORDER BY y DESC
limit 7

Answer 1

SELECT
meg_trade_name_2,
sum(CASE WHEN a.dt=DATE_FORMAT(adddate(now(),-1 ),'%Y%m%d')
THEN feed_cpc_fy_csm-feed_cpm_fy_csm-ppzx_fc_jp_csm-ppzx_jp_hy_csm ELSE 0 END)/1000 y
FROM apps.apps_v_summary_di a LEFT JOIN apps.apps_v_attack_city_white_list b on a.cust_id=b.cust_id
WHERE attack_city='city'
GROUP BY 1
ORDER BY y DESC
limit 7
DATE_FORMAT(adddate(now(),-1 ),'%Y%m%d')也可以写成current_date()-1或者DATE_FORMAT(current_date()-1,'%Y%m%d')