这是一道面试题,答案如下:
方法一:
使用窗口函数,按照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=''