时间戳

首页 > 解决方案 > 是否有满足两个 TRUE 条件的简单选择查询？

sql-server - 是否有满足两个 TRUE 条件的简单选择查询？

问题描述

我有一张记录表

FacilityID   StatusID    PaymentDuedate             EXPdate
0015617       1         2020-07-01 00:00:00.000    2020-06-30
0015617       2         2018-07-01 00:00:00.000    2020-06-30
0015617       5         2018-07-01 00:00:00.000    2020-06-30

(1,2) 中的记录状态和最新的付款到期日记录选择单个记录

在下面尝试但它满足 2 个条件为 TRUE 并且结果集是 2 条记录。有没有什么办法。我只需要作为 SQL VIEW。应用程序不支持存储的 PROC。

SELECT DISTINCT
       l.facility_id,
       [Report_Status_Id],
       MAX(payment_due_date) AS payment_due_date,
       CONVERT(date, DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, l.Expiration_Date), 0))) AS EXPdate
FROM a_license l
     JOIN (SELECT facility_id,
                  MAX(payment_due_date) AS payment_due_date,
                  [Report_Status_Id]
           FROM a_facility_annual_report
           GROUP BY facility_id,
                    [Report_Status_Id]) ar ON l.facility_id = ar.facility_id --and ar.payment_due_date=l.Expiration_Date
WHERE ar.[Report_Status_Id] IN (1, 2)
  AND l.facility_id IN ('0015617')
GROUP BY l.facility_id,
         [Report_Status_Id],
         Expiration_Date;

Output I need

    FacilityID    PaymentDuedate             EXPdate
     0015617    2020-07-01 00:00:00.000    2020-06-30

标签： sql-servertsql

解决方案

您可以将 CROSS APPLY 与 TOP 1 一起使用：

SELECT 
       l.facility_id,
       [Report_Status_Id],
       MAX(payment_due_date) AS payment_due_date,
       CONVERT(date, DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, l.Expiration_Date), 0))) AS EXPdate
FROM a_license l
CROSS APPLY     (SELECT TOP 1
            facility_id,
                    payment_due_date,
                    [Report_Status_Id]
           FROM a_facility_annual_report afar
       WHERE l.facility_id = afar.facility_id
           ORDER BY payment_due_date  DESC) ar 

WHERE ar.[Report_Status_Id] IN (1, 2)
  AND l.facility_id IN ('0015617')
GROUP BY l.facility_id,
         [Report_Status_Id],
         Expiration_Date;

推荐阅读