sql - 选择没有日期范围重叠的行-6ren

sql - 选择没有日期范围重叠的行

转载作者：行者123 更新时间：2023-12-02 22:38:34

想象一下下面的Loans表:

BorrowerID       StartDate         DueDate
=============================================
1                2012-09-02        2012-10-01
2                2012-10-05        2012-10-21
3                2012-11-07        2012-11-09
4                2012-12-01        2013-01-01
4                2012-12-01        2013-01-14
1                2012-12-20        2013-01-06
3                2013-01-07        2013-01-22
3                2013-01-15        2013-01-18
1                2013-02-20        2013-02-24

对于那些一次只借过一笔贷款的人，我该如何选择不同的 BorrowerID？这包括只申请过一笔贷款的借款人，以及申请超过一笔贷款的借款人，前提是如果你要为他们的贷款划出一个时间表，那么这些贷款都不会重叠。例如，在上表中，它应该只找到借款人 1 和 2。

我尝试过将表连接到自身，但还没有真正成功。非常感谢任何指点!

最佳答案

使用主键的 dbo.Loan 解决方案

要解决此问题，您需要采用两步方法，详细信息如下 SQL Fiddle 。我确实在示例数据中添加了一个 LoanId 列，并且查询要求存在这样一个唯一的 ID。如果没有，则需要调整连接子句以确保贷款不会与其自身匹配。

MS SQL Server 2008 架构设置:

CREATE TABLE dbo.Loans
    (LoanID INT, [BorrowerID] int, [StartDate] datetime, [DueDate] datetime)
GO

INSERT INTO dbo.Loans
    (LoanID, [BorrowerID], [StartDate], [DueDate])
VALUES
    (1, 1, '2012-09-02 00:00:00', '2012-10-01 00:00:00'),
    (2, 2, '2012-10-05 00:00:00', '2012-10-21 00:00:00'),
    (3, 3, '2012-11-07 00:00:00', '2012-11-09 00:00:00'),
    (4, 4, '2012-12-01 00:00:00', '2013-01-01 00:00:00'),
    (5, 4, '2012-12-01 00:00:00', '2013-01-14 00:00:00'),
    (6, 1, '2012-12-20 00:00:00', '2013-01-06 00:00:00'),
    (7, 3, '2013-01-07 00:00:00', '2013-01-22 00:00:00'),
    (8, 3, '2013-01-15 00:00:00', '2013-01-18 00:00:00'),
    (9, 1, '2013-02-20 00:00:00', '2013-02-24 00:00:00')
GO

首先，您需要找出哪些贷款与另一笔贷款重叠。查询使用 <=比较开始日期和截止日期。如果第二笔贷款在第一笔贷款结束的同一天开始，则该贷款将被视为重叠。如果您需要这些不重叠，请使用 <相反，在这两个地方。

查询 1:

SELECT 
   *,
   CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 
                     WHERE L2.BorrowerID = L1.BorrowerID
                       AND L2.LoanID <> L1.LoanID
                       AND L1.StartDate <= L2.DueDate
                       AND L2.StartDate <= l1.DueDate) 
        THEN 1
        ELSE 0
   END AS HasOverlappingLoan
  FROM dbo.Loans L1;

<强> Results :

| LOANID | BORROWERID |                        STARTDATE |                         DUEDATE | HASOVERLAPPINGLOAN |
|--------|------------|----------------------------------|---------------------------------|--------------------|
|      1 |          1 | September, 02 2012 00:00:00+0000 |  October, 01 2012 00:00:00+0000 |                  0 |
|      2 |          2 |   October, 05 2012 00:00:00+0000 |  October, 21 2012 00:00:00+0000 |                  0 |
|      3 |          3 |  November, 07 2012 00:00:00+0000 | November, 09 2012 00:00:00+0000 |                  0 |
|      4 |          4 |  December, 01 2012 00:00:00+0000 |  January, 01 2013 00:00:00+0000 |                  1 |
|      5 |          4 |  December, 01 2012 00:00:00+0000 |  January, 14 2013 00:00:00+0000 |                  1 |
|      6 |          1 |  December, 20 2012 00:00:00+0000 |  January, 06 2013 00:00:00+0000 |                  0 |
|      7 |          3 |   January, 07 2013 00:00:00+0000 |  January, 22 2013 00:00:00+0000 |                  1 |
|      8 |          3 |   January, 15 2013 00:00:00+0000 |  January, 18 2013 00:00:00+0000 |                  1 |
|      9 |          1 |  February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 |                  0 |

现在，利用该信息，您可以通过此查询确定没有重叠贷款的借款人:

查询 2:

WITH OverlappingLoans AS (
  SELECT 
   *,
   CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 
                     WHERE L2.BorrowerID = L1.BorrowerID
                       AND L2.LoanID <> L1.LoanID
                       AND L1.StartDate <= L2.DueDate
                       AND L2.StartDate <= l1.DueDate) 
        THEN 1
        ELSE 0
   END AS HasOverlappingLoan
  FROM dbo.Loans L1
),
OverlappingBorrower AS (
  SELECT BorrowerID, MAX(HasOverlappingLoan) HasOverlappingLoan
    FROM OverlappingLoans
   GROUP BY BorrowerID
)
SELECT * 
  FROM OverlappingBorrower
 WHERE hasOverlappingLoan = 0;

或者，您甚至可以通过计算贷款数量以及计算数据库中每个借款人与其他贷款重叠的贷款数量来获取更多信息。 (注意，如果贷款A和贷款B重叠，则该查询将两者都算作重叠贷款)

<强> Results :

| BORROWERID | HASOVERLAPPINGLOAN |
|------------|--------------------|
|          1 |                  0 |
|          2 |                  0 |

查询 3:

WITH OverlappingLoans AS (
  SELECT 
   *,
   CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 
                     WHERE L2.BorrowerID = L1.BorrowerID
                       AND L2.LoanID <> L1.LoanID
                       AND L1.StartDate <= L2.DueDate
                       AND L2.StartDate <= l1.DueDate) 
        THEN 1
        ELSE 0
   END AS HasOverlappingLoan
  FROM dbo.Loans L1
)
SELECT BorrowerID,COUNT(1) LoanCount, SUM(hasOverlappingLoan) OverlappingCount
  FROM OverlappingLoans
 GROUP BY BorrowerID;

<强> Results :

| BORROWERID | LOANCOUNT | OVERLAPPINGCOUNT |
|------------|-----------|------------------|
|          1 |         3 |                0 |
|          2 |         1 |                0 |
|          3 |         3 |                2 |
|          4 |         2 |                2 |

<小时/><小时/>

没有 PRIMARY KEY 的 dbo.Loan 解决方案

更新:由于该要求实际上需要一个不依赖于每笔贷款的唯一标识符的解决方案，因此我进行了以下更改:

1) 我添加了一个借款人，该借款人拥有两笔具有相同开始日期和到期日期的贷款

SQL Fiddle

MS SQL Server 2008 架构设置:

CREATE TABLE dbo.Loans
    ([BorrowerID] int, [StartDate] datetime, [DueDate] datetime)
GO

INSERT INTO dbo.Loans
    ([BorrowerID], [StartDate], [DueDate])
VALUES
    ( 1, '2012-09-02 00:00:00', '2012-10-01 00:00:00'),
    ( 2, '2012-10-05 00:00:00', '2012-10-21 00:00:00'),
    ( 3, '2012-11-07 00:00:00', '2012-11-09 00:00:00'),
    ( 4, '2012-12-01 00:00:00', '2013-01-01 00:00:00'),
    ( 4, '2012-12-01 00:00:00', '2013-01-14 00:00:00'),
    ( 1, '2012-12-20 00:00:00', '2013-01-06 00:00:00'),
    ( 3, '2013-01-07 00:00:00', '2013-01-22 00:00:00'),
    ( 3, '2013-01-15 00:00:00', '2013-01-18 00:00:00'),
    ( 1, '2013-02-20 00:00:00', '2013-02-24 00:00:00'),
    ( 5, '2013-02-20 00:00:00', '2013-02-24 00:00:00'),
    ( 5, '2013-02-20 00:00:00', '2013-02-24 00:00:00')
GO

2) 这些“同等日期”贷款需要额外的步骤:

查询 1:

SELECT BorrowerID, StartDate, DueDate, COUNT(1) LoanCount
  FROM dbo.Loans
 GROUP BY BorrowerID, StartDate, DueDate;

<强> Results :

| BORROWERID |                        STARTDATE |                         DUEDATE | LOANCOUNT |
|------------|----------------------------------|---------------------------------|-----------|
|          1 | September, 02 2012 00:00:00+0000 |  October, 01 2012 00:00:00+0000 |         1 |
|          1 |  December, 20 2012 00:00:00+0000 |  January, 06 2013 00:00:00+0000 |         1 |
|          1 |  February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 |         1 |
|          2 |   October, 05 2012 00:00:00+0000 |  October, 21 2012 00:00:00+0000 |         1 |
|          3 |  November, 07 2012 00:00:00+0000 | November, 09 2012 00:00:00+0000 |         1 |
|          3 |   January, 07 2013 00:00:00+0000 |  January, 22 2013 00:00:00+0000 |         1 |
|          3 |   January, 15 2013 00:00:00+0000 |  January, 18 2013 00:00:00+0000 |         1 |
|          4 |  December, 01 2012 00:00:00+0000 |  January, 01 2013 00:00:00+0000 |         1 |
|          4 |  December, 01 2012 00:00:00+0000 |  January, 14 2013 00:00:00+0000 |         1 |
|          5 |  February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 |         2 |

3) 现在，由于每个贷款范围都是独一无二的，我们可以再次使用旧技术。然而，我们还需要考虑那些“同等日期”贷款。 (L1.StartDate <> L2.StartDate OR L1.DueDate <> L2.DueDate)防止贷款与其自身匹配。 OR LoanCount > 1考虑“同等日期”贷款。

查询 2:

WITH NormalizedLoans AS (
  SELECT BorrowerID, StartDate, DueDate, COUNT(1) LoanCount
    FROM dbo.Loans
   GROUP BY BorrowerID, StartDate, DueDate  
)
SELECT 
   *,
   CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 
                     WHERE L2.BorrowerID = L1.BorrowerID
                       AND L1.StartDate <= L2.DueDate
                       AND L2.StartDate <= l1.DueDate
                       AND (L1.StartDate <> L2.StartDate
                            OR L1.DueDate <> L2.DueDate)
                   ) 
             OR LoanCount > 1
        THEN 1
        ELSE 0
   END AS HasOverlappingLoan
  FROM NormalizedLoans L1;

<强> Results :

| BORROWERID |                        STARTDATE |                         DUEDATE | LOANCOUNT | HASOVERLAPPINGLOAN |
|------------|----------------------------------|---------------------------------|-----------|--------------------|
|          1 | September, 02 2012 00:00:00+0000 |  October, 01 2012 00:00:00+0000 |         1 |                  0 |
|          1 |  December, 20 2012 00:00:00+0000 |  January, 06 2013 00:00:00+0000 |         1 |                  0 |
|          1 |  February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 |         1 |                  0 |
|          2 |   October, 05 2012 00:00:00+0000 |  October, 21 2012 00:00:00+0000 |         1 |                  0 |
|          3 |  November, 07 2012 00:00:00+0000 | November, 09 2012 00:00:00+0000 |         1 |                  0 |
|          3 |   January, 07 2013 00:00:00+0000 |  January, 22 2013 00:00:00+0000 |         1 |                  1 |
|          3 |   January, 15 2013 00:00:00+0000 |  January, 18 2013 00:00:00+0000 |         1 |                  1 |
|          4 |  December, 01 2012 00:00:00+0000 |  January, 01 2013 00:00:00+0000 |         1 |                  1 |
|          4 |  December, 01 2012 00:00:00+0000 |  January, 14 2013 00:00:00+0000 |         1 |                  1 |
|          5 |  February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 |         2 |                  1 |

此查询逻辑没有改变(除了切换开头)。

查询 3:

WITH NormalizedLoans AS (
  SELECT BorrowerID, StartDate, DueDate, COUNT(1) LoanCount
    FROM dbo.Loans
   GROUP BY BorrowerID, StartDate, DueDate  
),
OverlappingLoans AS (
SELECT 
   *,
   CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 
                     WHERE L2.BorrowerID = L1.BorrowerID
                       AND L1.StartDate <= L2.DueDate
                       AND L2.StartDate <= l1.DueDate
                       AND (L1.StartDate <> L2.StartDate
                            OR L1.DueDate <> L2.DueDate)
                   ) 
             OR LoanCount > 1
        THEN 1
        ELSE 0
   END AS HasOverlappingLoan
  FROM NormalizedLoans L1
),
OverlappingBorrower AS (
  SELECT BorrowerID, MAX(HasOverlappingLoan) HasOverlappingLoan
    FROM OverlappingLoans
   GROUP BY BorrowerID
)
SELECT * 
  FROM OverlappingBorrower
 WHERE hasOverlappingLoan = 0;

<强> Results :

| BORROWERID | HASOVERLAPPINGLOAN |
|------------|--------------------|
|          1 |                  0 |
|          2 |                  0 |

4) 在此计数查询中，我们需要再次合并“同等日期”贷款计数。为此，我们使用 SUM(LoanCount)而不是简单的 COUNT 。我们还必须乘以 hasOverlappingLoan与 LoanCount 一起再次获得正确的重叠计数。

查询 4:

WITH NormalizedLoans AS (
  SELECT BorrowerID, StartDate, DueDate, COUNT(1) LoanCount
    FROM dbo.Loans
   GROUP BY BorrowerID, StartDate, DueDate  
),
OverlappingLoans AS (
SELECT 
   *,
   CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 
                     WHERE L2.BorrowerID = L1.BorrowerID
                       AND L1.StartDate <= L2.DueDate
                       AND L2.StartDate <= l1.DueDate
                       AND (L1.StartDate <> L2.StartDate
                            OR L1.DueDate <> L2.DueDate)
                   ) 
             OR LoanCount > 1
        THEN 1
        ELSE 0
   END AS HasOverlappingLoan
  FROM NormalizedLoans L1
)
SELECT BorrowerID,SUM(LoanCount) LoanCount, SUM(hasOverlappingLoan*LoanCount) OverlappingCount
  FROM OverlappingLoans
 GROUP BY BorrowerID;

<强> Results :

| BORROWERID | LOANCOUNT | OVERLAPPINGCOUNT |
|------------|-----------|------------------|
|          1 |         3 |                0 |
|          2 |         1 |                0 |
|          3 |         3 |                2 |
|          4 |         2 |                2 |
|          5 |         2 |                2 |

我强烈建议找到一种方法来使用我的第一个解决方案，因为没有主键的贷款表是一种“奇怪”的设计。但是，如果您确实无法到达那里，请使用第二种解决方案。

关于sql - 选择没有日期范围重叠的行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19351019/

文章推荐： sql - 在 SQL 用户定义函数中使用公用表表达式时出错

文章推荐： javascript - get() 方法对 CollectionReference 有何作用？

文章推荐： javascript - 为什么我的集合组查询没有结果？

sql - SQL、PL-SQL 和 T-SQL 之间有什么区别？
SQL、PL-SQL 和 T-SQL 之间有什么区别？谁能解释一下这三者之间的区别，并提供每一个的相关使用场景？最佳答案 SQL 是一种对集合进行操作的查询语言。它或多或少是标准化的，几乎所有关
sql - T-SQL、SQL Server 和 SQL 有什么区别
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。我对 SQL 的了解足以完成我的
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列有一个默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任
sql - 在字符串中查找第二组数字(SQL/PL-SQL)
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
sql - 服务器端 sql 与客户端 sql
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈，无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开，visit the help center . 9年前关闭
sql - sql 如何计算 sql 存储过程中的附加表？
我有一个存储过程(称为 sprocGetArticles)，它从文章表中返回文章列表。这个存储过程没有任何参数。用户可以对每篇文章发表评论，我将这些评论存储在由文章 ID 链接的评论表中。有什么方
sql - 嵌入式 SQL 与动态 SQL
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。我自己做了一些数据库工作
sql - SQL Server SQL 语句可以有多少个字符？
SQL Server 中 SQL 语句的最大长度是多少？这个长度是否取决于 SQL Server 的版本？例如，在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
sql-server - SQL 行到列 sql
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
sql - SQL Server SQL 语句中的动态日期
预先感谢您对此提供的任何帮助。假设我有一个查询，可以比较跨年的数据，从某个任意年份开始，永无止境(进入 future )，每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列的默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任何数
sql - (SQL Server) SQL 不允许在检查过程是否存在后创建过程
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
sql - 动态 SQL 是否比 SQL Server 中的静态 SQL 性能更高？
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快，所以我经常看到 DSQL 到处都是。除了明显的缺点，比如在运行之前无法检测到错误并且更难阅读，这是否准确？当我问他为什么一直使用
sql - exec sp_executesql @sql 和 exec (@sql) SQL Server
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
sql - 动态 SQL - EXEC(@SQL) 与 EXEC SP_EXECUTESQL(@SQL)
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ？最佳答案 sp_executes
c# - SQL > Linq to Sql，SQL 查询有效，Linq to SQL 返回空数据集
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
sql-server - "> sql.txt && sql -h-1 -i sql.txt && del sql.txt"命令是什么意思？
我正在调试一些构建成功运行的 SQL 命令的代码。然而，在查询结束时，查询结果似乎被写入了一个文本文件。完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
sql - 如何从 MS SQL 数据库(Microsoft SQL Server)中的其他 .sql 文件运行 .sql 文件？
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
sql - 如何在查询中使用 SQL 变量(SQL Server)？
我写了下面的 SQL 存储过程，它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
sql - 如何将两个列表转换为邻接矩阵 SQL Server T-SQL？
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表，如下所示 Fruit Colour Apple Red Orange

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城