SQL连表更新多个平均值字段

2021/12/1 19:11:10

本文主要是介绍SQL连表更新多个平均值字段,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

UPDATE res2_product AS a LEFT JOIN (SELECT
    pro_id,
    IF(
        SUM( transaction_date > DATE_SUB( CURRENT_DATE, INTERVAL 8 DAY ) ), 
        SUM( IF ( transaction_date > DATE_SUB( CURRENT_DATE, INTERVAL 8 DAY ), transaction_quantity, 0 ) ) / SUM( transaction_date > DATE_SUB( CURRENT_DATE, INTERVAL 8 DAY ) ),
        0)
         AS avg_stock_7,
    AVG( transaction_quantity ) AS avg_stock_30 
FROM
    get2_transaction_daily 
WHERE
    transaction_date > DATE_SUB( CURRENT_DATE, INTERVAL 31 DAY ) GROUP BY pro_id) AS b ON a.pro_id = b.pro_id SET a.avg_stock_7 = IFNULL(b.avg_stock_7, 0), a.avg_stock_30 = IFNULL(b.avg_stock_30, 0)



这篇关于SQL连表更新多个平均值字段的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程