返回第二高日期

Question

我有一个销售表，它显示下面的信息

销售人员750022年10月19日643022年9月17日550022年3月15日//tr>443022年2月13日250022年1月22日//tr>3102022年2月5日112<2022年1月7日>

Answer 1

我们使用rank()来查找最近的几次销售，然后调整结果。

select    max(recent_ID_sale)                         as ID_sale
,sales_person
,max(recent_sale)                            as recent_sale
,coalesce(max(last_sale), max(recent_sale))  as last_sale
from
(
select    sales_person
,case when rank() over(partition by sales_person order by sale_date desc) = 1 then sale_date end as recent_sale
,case when rank() over(partition by sales_person order by sale_date desc) = 2 then sale_date end as last_sale
,case when rank() over(partition by sales_person order by sale_date desc) = 1 then ID_sale   end as recent_ID_sale
from      t
) t
group by sales_person
order by recent_sale desc

最后销售

ID_sale	销售人员	最近销售
1	50	2022-10-19	2022-03-15
2	43	2022-09-17	2022-02-13
6	10	2022-02-05	20222-05
7	12	2022-01-07	2022:01-07

您可以先添加一个CTE，在其中添加一个row_number以获得日期顺序，然后选择它们

更新

我添加了一个没有GROUP BY的解决方案，并在末尾添加了更多的窗口功能

CREATE TABLE t
(ID_sale int, sales_person int, sale_date date)
;

INSERT INTO t
(ID_sale, sales_person, sale_date)
VALUES
(1, 50, '2022-10-19'),
(2, 43, '2022-9-17'),
(3, 50, '2022-3-15'),
(4, 43, '2022-2-13'),
(5, 50, '2022-1-22'),
(6, 10, '2022-2-5'),
(7, 12, '2022-1-7')
;

Records: 7  Duplicates: 0  Warnings: 0

table class="s-table">MIN1502022-192022-03-152022-09-17年2022-02-052022-01-07

使用ROW_NUMBER()筛选最近的行，使用LEAD()筛选last_sale。对于不存在last_sale的情况，使用COALESCE()：

with cte as (
select
ID_sale,
sales_person,
sale_date as recent_sale,
lead(sale_date) over w as last_sale,
row_number() over w as rn
from sales
window w as (partition by sales_person order by sale_date desc)
)
select 
ID_sale,
sales_person,
recent_sale,
coalesce(last_sale, recent_sale) as last_sale
from cte
where rn = 1
order by ID_sale;

对于不支持窗口函数的旧版本(如ROW_NUMBER()和LEAD()(，您可以在WHERE子句中为lastrongale和最近的行筛选器使用相关(按sales_person(子查询：

select 
s.ID_sale,
s.sales_person,
s.sale_date as recent_sale,
coalesce((
select
max(sale_date)
from sales s2
where s2.sales_person = s.sales_person
and s2.sale_date    < s.sale_date
), s.sale_date) as last_sale
from sales s
where sale_date = (
select max(sale_date)
from sales s1
where s1.sales_person = s.sales_person
)
order by ID_sale;

效率不如窗口函数，但更简单且可移植：

select max(t1.ID_sale), t1.sales_person, t1.recent_sale, max(t2.recent_sale) as last_sale
from mytable t1
left join mytable t2 on t2.sales_person = t1.sales_person
and t2.ID_sale < t1.ID_sale
group by 2, 3

除非你有一亿的销售额，否则如果你有一个关于sales_person的索引，这会很好。