时间戳

首页 > 解决方案 > 查找搜索日期之间的对应行

mysql - 查找搜索日期之间的对应行

问题描述

我有两个具有这种相关结构和数据的表

CREATE TABLE "beneficiaries" (
"id" integer not null primary key autoincrement
);

CREATE TABLE "beneficiary_dates" (
"id" integer not null primary key autoincrement,
"opening_date" datetime null,
"closing_date" datetime null,
"beneficiary_id" integer not null,
foreign key("beneficiary_id") references "beneficiaries"("id")
)

INSERT INTO "beneficiary_dates" VALUES (1,'2020-02-01 15:08:28','2020-02-05 19:21:51',1);
INSERT INTO "beneficiary_dates" VALUES (2,'2020-04-01 15:08:28',NULL,2);
INSERT INTO "beneficiary_dates" VALUES (3,'2020-04-01 15:08:28','2020-04-29 19:21:51',3);
INSERT INTO "beneficiary_dates" VALUES (4,'2020-06-01 15:08:28',NULL,4);

我在用户可以通过的前端创建一个搜索过滤器

现在我正在尝试检索在开始日期和结束日期之间打开的受益人。已打开的受益人是与开始和结束搜索日期所在的期间（opening_date -- closing_date）匹配的受益人。

例如：

beneficiary_dates 中的行

1 | 2020-02-01 | 2020-02-05 | 1
2 | 2020-04-01 | null | 2
3 | 2020-04-01 | 2020-04-29 | 3
4 | 2020-06-01 | null | 4

SQL 请求必须返回第 1-2-3 行

标签： mysqlsql

解决方案

根据您的数据和所需的输出，它可能类似于

DECLARE @InOpeningDate DATE='2020-01-01';
DECLARE @InClosingDate DATE='2020-05-01';

WITH BENEFICIARY_DATES(ID,OPENING_DATE,CLOSING_DATE,BENEFICIARY_ID) AS 
 ( 
   SELECT 1, '2020-02-01', '2020-02-05' , 1
      UNION ALL
   SELECT 2 , '2020-04-01', null ,2
      UNION ALL
   SELECT 3, '2020-04-01' ,'2020-04-29', 3
      UNION ALL
   SELECT 4 , '2020-06-01' , null , 4
)
SELECT B.ID,B.OPENING_DATE,B.CLOSING_DATE,B.BENEFICIARY_ID
   FROM BENEFICIARY_DATES AS B
    WHERE B.OPENING_DATE BETWEEN @InOpeningDate AND @InClosingDate
     AND ISNULL(B.CLOSING_DATE,@InClosingDate)<=@InClosingDate

我还建议您对 CloseDate 列使用类似 '9999-12-31' 而不是 NULL

推荐阅读