在 SQLITE 中使用窗口函数 [英] Working with window function in SQLITE
本文介绍了在 SQLITE 中使用窗口函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这组数据
purchasingid date supplierid
1 2014-01-01 12
2 2014-01-01 13
3 2013-12-06 12
4 2013-12-05 11
5 2014-01-01 17
6 2013-12-05 12
我想查看所有在 2014-01-01 购买的供应商,他们之前的订单日期是什么时候.如果它不存在,则将其留空.
I want to check for all suppliers who bought on 2014-01-01 what was the date of their previous order. If it doesn't exists leave it blank.
意思是我想得到:
supplierid date last_time_buy_date
12 2014-01-01 2013-12-06
13 2014-01-01
17 2014-01-01
supplierid 11 没有在 2014-01-01 购买所以他根本没有出现.
supplierid 11 did not buy on 2014-01-01 so he does not appear at all.
这就是我所做的:
select supplierid,date, max(date)
from purchasing
where supplierid in (select supplierid
from purchasing
where date='2014-01-01')
这不起作用.我知道我应该以某种方式使用窗口函数,但我不知道如何...有什么想法吗?
This doesnt work. I know I should use window function somehow but I don't know how to... Any thoughts?
推荐答案
SQLite 不支持窗口函数.相反,您可以这样做:
SQLite doesn't support window functions. Instead, you can do:
select p.*,
(select max(p2.date)
from purchasing p2
where p2.supplierid = p.supplierid and
p2.date < p.date
) as prev_date
from purchasing p
where p.date = '2014-01-01';
这篇关于在 SQLITE 中使用窗口函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文