mysql - 获取按日期分组的列的总和

Question

想知道是否有办法获取具有相同 date_of_export 的项目的 stock_case 列的总和？

在这里更新了 fiddle 和一些相关数据:

https://www.db-fiddle.com/f/szC1Ftj3ZGEC24gSYp6ad4/4

预期的输出是这样的:

这是使用的查询

    SELECT 
    st.product_code,
    st.date_of_export,
    st.best_before_date,
    st.stock_case,
    (
    SELECT 
            SUM(st2.stock_case)
        FROM
            stock_tracking AS st2
            WHERE
            st2.product_code IN ('MGN003')
        AND MONTH(st2.date_of_export) IN (07)
        AND YEAR(st2.date_of_export) IN (2018)
        AND st2.stock_case != 0
    ) AS total
    FROM
    stock_tracking st
WHERE
    product_code IN ('MGN003')
        AND MONTH(st.date_of_export) IN (07)
        AND YEAR(st.date_of_export) IN (2018)
        AND stock_case != 0

和我的结果

想要总计列，例如 16、16、16、...、19 等对于另一种情况，我使用了这样的子查询

SELECT 
    d.products_name,
    stock_case,
    st.date_of_export,
    st.best_before_date,
    st.product_code,
    (SELECT 
            SUM(st2.stock_case)
        FROM
            stock_tracking AS st2
        WHERE
            DATE(st2.date_of_export) = (SELECT 
                    DATE(tmp.last_update)
                FROM
                    (SELECT 
                        date_of_export AS last_update
                    FROM
                        stock_tracking
                    ORDER BY date_of_export DESC
                    LIMIT 1) AS tmp
                WHERE
                    product_code = 'MGN003')) AS total
FROM
    stock_tracking st
        LEFT JOIN
    products AS p ON p.products_model = st.product_code
        LEFT JOIN
    products_description AS d ON d.products_id = p.products_id
WHERE
    product_code = 'MGN003'
        AND d.language_id = 2
        AND DATE(st.date_of_export) = (SELECT 
            DATE(tmp.last_update)
        FROM
            (SELECT 
                date_of_export AS last_update
            FROM
                stock_tracking AS st
            ORDER BY date_of_export DESC
            LIMIT 1) AS tmp)

结果如下:

Answer 1

您可以在 date_of_export 之前编写一个子查询到 sum(stock_case)，然后在 Date self join，然后你就可以得到你期望的结果。

SELECT  
    s.product_name,
    s.date_of_export,
    s.best_before_date,
    s.product_code,
    s.stock_case,
    t.totle
FROM
    stock_tracking s
INNER JOIN
    (
        SELECT SUM(stock_case) totle,date_of_export dt
       FROM stock_tracking
       where   
        product_code = 'MGN003'
        AND MONTH(date_of_export) =07
        AND YEAR(date_of_export) =2018
        AND stock_case != 0
      GROUP BY date_of_export
    ) t on DATE_FORMAT(s.date_of_export, "%d-%m-%Y") = DATE_FORMAT(t.dt, "%d-%m-%Y")
where 
    s.product_code = 'MGN003'
AND MONTH(s.date_of_export) =07
AND YEAR(s.date_of_export) =2018
AND s.stock_case != 0

sqlfiddle