sql - 如何在 SQL 中获取最接近请求参数的值？

Question

我有一张表 (shop_staff_relationvm)，它保存了员工和商店之间的关系。

它有 3 列:id_shop、id_staff 和 change_date。

• id_staff => 员工外键
• id_shop => 商店外键
• change_date ==> 每当创建新的员工-商店关系时，我都会保存设置日期(员工的商店可能会更改多次)

上面的 3 列表示 shop_staff_relationvm 表的可嵌入主键。

在查询中，我有 2 个请求参数:idShop 和一个日期。

我正在尝试获取特定月份 (requestMonth) 属于特定商店 (idShop) 的员工列表。

因此，当 requestMonth 介于员工和商店之间关系的第一个日期和关系的最后一个日期之间时，我需要获取员工列表。

这是一个例子:

STAFF     | SHOP     | CHANGE DATE
----------+----------+-------------
Staff A   | Shop 1   | 10-01-2019
Staff A   | Shop 2   | 10-03-2019
Staff A   | Shop 3   | 10-05-2019

假设我们有 requestMonth = 02-2019

然后:商店 1 将有员工 A，商店 2 将没有员工，商店 3 将没有员工

假设我们有 requestMonth = 03-2019

然后:1号店没有员工，2号店有员工A，3号店没有员工

假设我们有 requestMonth = 06-2019

然后:商店 1 没有员工，商店 2 没有员工，商店 3 有员工 A

这是我想出的公式:

first date of relation <= requestMonth <= first date of next relation

我在获取关系的第一个日期时没有问题，但在获取下一个关系的第一个日期时遇到了问题(我认为它是关系第一个日期的下一个最接近的值)。

PS:我使用的是JPA原生查询

我已经尝试使用 order by (change_date - first date of relation) 来获取第一个最接近关系的第一个日期的值。但它总是让我得到最大值(在我上面提到的例子中，它总是让我得到 05-2019 月)

我还尝试使用 LEAD 获取下一个最接近的值。但是，使用上面的示例:

For requestMonth = 02-2019 : the LEAD was equal to 03-2019 => correct

For requestMonth = 03-2019 : the LEAD was equal to 01-2019 => wrong => It should be 05-2019

这是整个查询:

SELECT s.* FROM staffvm s INNER JOIN shop_staff_relationvm ss ON ss.id_staff = s.id_staff WHERE ss.id_shop = 2 and 
(CASE WHEN ((SELECT MAX(to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff) > to_date('2019-02-16', 'yyyy-MM') 
and (SELECT MIN(to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff) <= to_date('2019-02-16', 'yyyy-MM') ) 
THEN 
((SELECT ((LEAD (to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) OVER (ORDER BY ss.id_staff DESC)) > to_date('2019-02-16', 'yyyy-MM') ) 
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop !=2 LIMIT 1)
and 
(SELECT (MIN(to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) <= to_date('2019-02-16', 'yyyy-MM'))
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop = 2) ) 
ELSE 
(SELECT (to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM') <= to_date('2019-02-16', 'yyyy-MM')) 
 FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop = 2 ) 
END);

我遇到问题的查询部分:

((SELECT ((LEAD (to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) OVER (ORDER BY ss.id_staff DESC)) > to_date('2019-02-16', 'yyyy-MM') ) 
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop !=2 LIMIT 1)
and 
(SELECT (MIN(to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) <= to_date('2019-02-16', 'yyyy-MM'))
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop = 2))

Answer 1

如果你想得到特定日期的员工，你可以使用distinct on。

select distinct on (ss.id_staff) ss.*
from shop_staff_relationvm ss 
where ss.change_date <= '2019-02-01'::date
order by ss.id_staff, ss.change_date desc;

使用 shop_staff_relationvm(id_staff, change_date) 上的索引，这可能是可能的解决方案中性能最好的。

要确定谁在特定商店，请使用子查询:

select s.*
from (select distinct on (ss.id_staff) ss.*
      from shop_staff_relationvm ss 
      where ss.change_date <= '2019-02-01'::date
      order by ss.id_staff, ss.change_date desc
     ) s
where s.shop_id = 2;