SELECT查询IF ELSE逻辑实现：筛选7天内到期的过滤器

需求：筛选未来7天内即将到期的过滤器，规则为：

• Filter表的filterExp字段为默认到期天数
• FilterChange表的filterExpCustom字段为用户自定义到期天数
• 优先级：若filterExpCustom非空则优先使用，否则使用filterExp

表数据示例 #

Filter表 #

INSERT [Filter] ([filterId], [filterName], [filterExp]) 
VALUES (1, N'pp', 6)
INSERT [Filter] ([filterId], [filterName], [filterExp]) 
VALUES (2, N'Carbonate', 5)
INSERT [Filter] ([filterId], [filterName], [filterExp]) 
VALUES (3, N'Carbon Block', 5)
INSERT [Filter] ([filterId], [filterName], [filterExp]) 
VALUES (4, N'Carbon Post', 12)
INSERT [Filter] ([filterId], [filterName], [filterExp]) 
VALUES (5, N'Mineral', 12)

FilterChange表 #

INSERT [FilterChange] ([filterChnageId], [customerId], [filterId], [customerDeviceId], [filterChangeDate], [filterExpCustom]) 
VALUES (186, 3, 2, 65, CAST(N'2023-01-31' AS Date), 7)
INSERT [FilterChange] ([filterChnageId], [customerId], [filterId], [customerDeviceId], [filterChangeDate], [filterExpCustom]) 
VALUES (187, 3, 5, 65, CAST(N'2023-01-31' AS Date), NULL)
INSERT [FilterChange] ([filterChnageId], [customerId], [filterId], [customerDeviceId], [filterChangeDate], [filterExpCustom]) 
VALUES (188, 2, 3, 66, CAST(N'2023-02-01' AS Date), 10)
INSERT [FilterChange] ([filterChnageId], [customerId], [filterId], [customerDeviceId], [filterChangeDate], [filterExpCustom]) 
VALUES (189, 2, 3, 66, CAST(N'2023-02-01' AS Date), NULL)

现有存储问题 #

当前存储过程[dbo].[FilterExpirationGetList]仅使用默认到期天数filterExp，未处理用户自定义到期天数filterExpCustom的优先级逻辑，需要修改查询逻辑。

现有存储过程代码 #

CREATE PROCEDURE [dbo].[FilterExpirationGetList]
  @userName BIGINT
AS
  DECLARE @InNextDays int
BEGIN
    SET @InNextDays = 7

  SELECT f.filterId,f.filterName, f.filterExp,
    fc.customerId, fc.customerDeviceId, fc.filterChangeDate, fc.filterExpCustom,
    c.fName, c.lName, c.cMobile, c.userName
   FROM FilterChange fc INNER JOIN
     Filter f  ON fc.filterId = f.filterId  INNER JOIN
     Customer c ON c.CustomerId = fc.customerId
   WHERE c.userName = @userName AND
    DATEADD(DAY, DATEPART(DAY, GETDATE()) 
    - DATEPART(DAY, DATEADD(DAY,f.filterExp,fc.filterChangeDate)), 
    DATEADD(DAY,f.filterExp,fc.filterChangeDate))
          BETWEEN CONVERT(DATE, GETDATE()) 
           AND CONVERT(DATE, GETDATE() + @InNextDays); 
END

修改后的存储过程 #

核心修改点：

1. 用ISNULL(fc.filterExpCustom, f.filterExp)获取实际使用的到期天数，实现自定义天数优先
2. 简化日期计算逻辑，直接计算过滤器到期日期，判断是否落在当前日期到未来7天范围内

CREATE PROCEDURE [dbo].[FilterExpirationGetList]
  @userName BIGINT
AS
  DECLARE @InNextDays int
BEGIN
    SET @InNextDays = 7

  SELECT 
    f.filterId,
    f.filterName, 
    f.filterExp,
    fc.customerId, 
    fc.customerDeviceId, 
    fc.filterChangeDate, 
    fc.filterExpCustom,
    c.fName, 
    c.lName, 
    c.cMobile, 
    c.userName,
    -- 新增列：显示实际使用的到期天数
    ISNULL(fc.filterExpCustom, f.filterExp) AS actualExpDays,
    -- 新增列：显示到期日期
    DATEADD(DAY, ISNULL(fc.filterExpCustom, f.filterExp), fc.filterChangeDate) AS expirationDate
  FROM FilterChange fc 
  INNER JOIN Filter f ON fc.filterId = f.filterId  
  INNER JOIN Customer c ON c.CustomerId = fc.customerId
  WHERE 
    c.userName = @userName 
    AND DATEADD(DAY, ISNULL(fc.filterExpCustom, f.filterExp), fc.filterChangeDate) 
        BETWEEN CONVERT(DATE, GETDATE()) 
        AND CONVERT(DATE, DATEADD(DAY, @InNextDays, GETDATE())); 
END

修改说明 #

• 使用ISNULL(fc.filterExpCustom, f.filterExp)自动判断：如果filterExpCustom有值则用自定义天数，否则用默认天数
• 简化了到期日期的计算逻辑，直接基于更换日期加上实际到期天数得到到期日期，再判断是否在未来7天内，可读性更高
• 新增了actualExpDays和expirationDate列，方便查看实际使用的天数和到期日期，便于调试和业务查看

内容的提问来源于stack exchange，提问作者Reza Paidar