我有一个表,显示了每天的库存记录。我需要得到滚动的区别和它的总数。我怎样才能拿到它?递归SQL?
这里我有一个简化的表格,如下所示:
SELECT timestamp, item FROM inventory;
时间戳 | ||
---|---|---|
20210101 | A | |
20210101 | B | |
20210101 | C | |
20210102 | A | |
20210103 | B | |
20210103 | D |
您的示例数据与结果不匹配。。。低于的sql server解决方案
CREATE TABLE inventory (
timestamp varchar(8),
item varchar(1)
);
insert into inventory(timestamp, item) values
('20210101','A'),
('20210101','A'),
('20210101','B'),
('20210101','C'),
('20210102','A'),
('20210103','B'),
('20210103','D');
select timestamp, sum(qtd) as qtd, string_agg(item,',') items
from(
select
timestamp, item, 1 qtd
from inventory
group by timestamp, item) s
group by timestamp