SQL计算每天的期初、入库、出库、结存数

这是一道面试题，答案如下：

方法一：

使用窗口函数，按照t1.ru_date>t2.ru_date自关联，使用窗口函数计算当天之前的所有入库之和、所有出库之和，再配合ROW_NUMBER() 窗口函数倒序，去行号为1的，详情如下SQL

WITH table_test AS (
	SELECT '2017-02-1' AS ru_date,200 AS ru_ku, 10 AS chu_ku
	UNION
	SELECT '2017-02-2' AS ru_date,10 AS ru_ku, 30 AS chu_ku
	UNION
	SELECT '2017-02-5' AS ru_date,50 AS ru_ku, 10 AS chu_ku
	UNION
	SELECT '2017-02-6' AS ru_date,60 AS ru_ku, 80 AS chu_ku
	UNION
	SELECT '2017-02-7' AS ru_date,10 AS ru_ku, 90 AS chu_ku
)

SELECT 
    ru_date 入库时间,
    sum_in-sum_out 期初,
    ru_ku 入库,
    chu_ku 出库,
    sum_in - sum_out + ru_ku - chu_ku 库存
FROM (
    SELECT 
        t1.*,
        SUM(IFNULL(t2.ru_ku,0)) OVER(PARTITION BY t1.ru_date ORDER BY t2.ru_date) sum_in,
        SUM(IFNULL(t2.chu_ku,0)) OVER(PARTITION BY t1.ru_date ORDER BY t2.ru_date) sum_out,
        ROW_NUMBER() OVER(PARTITION BY t1.ru_date ORDER BY t2.ru_date DESC) AS r
    FROM table_test t1
    LEFT JOIN table_test t2 ON t1.ru_date>t2.ru_date
) AS t
WHERE r=1

方法二：

更改sql_mode，直接用group by 即可，详情如下：

-- 更改sql_mode否则会报错
SET sql_mode='';

WITH table_test AS (
	SELECT '2017-02-1' AS ru_date,200 AS ru_ku, 10 AS chu_ku
	UNION
	SELECT '2017-02-2' AS ru_date,10 AS ru_ku, 30 AS chu_ku
	UNION
	SELECT '2017-02-5' AS ru_date,50 AS ru_ku, 10 AS chu_ku
	UNION
	SELECT '2017-02-6' AS ru_date,60 AS ru_ku, 80 AS chu_ku
	UNION
	SELECT '2017-02-7' AS ru_date,10 AS ru_ku, 90 AS chu_ku
)
SELECT 
	t1.ru_date '入库时间',
	SUM(IFNULL(t2.ru_ku,0))-SUM(IFNULL(t2.chu_ku,0)) AS '期初',
	t1.ru_ku '入库数',
	t1.chu_ku '出库数',
	SUM(IFNULL(t2.ru_ku,0))-SUM(IFNULL(t2.chu_ku,0))+t1.ru_ku-t1.chu_ku AS '库存'
FROM table_test t1
LEFT JOIN table_test t2 ON t1.ru_date>t2.ru_date
GROUP BY 1
;

因为sql_mode默认为full_group_by，只按一个字段分组会报错，所有加上SET sql_mode=''