在不使用 where 子句中的函数的情况下计算年龄-解网

问：

我一直在阅读有关避免在 WHERE 子句中使用函数的文章（因为它们通常必须使用扫描并且通常效率不高）。

我有一个 SP，它根据客户在过去几天是否过生日来计算他们的年龄。

我正在努力将生日的计算从 where 子句移动到 select 语句中。这是当前的 SP。

CREATE PROCEDURE dbo.SP_CalculateAge (
@LastNDays int
) AS
        UPDATE Customers
           SET CustomerAge = DATEDIFF (yy, CustomerBirthDate, GETDATE ())
         WHERE DATEADD (yy, DATEDIFF (yy, CustomerBirthDate, GETDATE ()), CustomerBirthDate) BETWEEN
            DATEADD (dd, ISNULL (@LastNDays, -4), GETDATE ()) AND GETDATE ();

我对它所创造的时代感到满意。查看其他建议，我还可以通过执行以下操作来计算 AGE

SELECT DATEDIFF(year, CustomerBirthDate, getdate()) + CASE WHEN (DATEADD(year,DATEDIFF(year, CustomerBirthDate, getdate()) , CustomerBirthDate) > getdate()) THEN - 1 ELSE 0 END
      from customers'

我的问题是我现在不确定我可以将搜索以查看生日是否发生在过去 4 天内的部分放在哪里。我只能在WHERE子句中想到它，而不是在SELECT中。

BETWEEN
            DATEADD (dd, ISNULL (@LastNDays, -4), GETDATE ()) AND GETDATE ();

我假设我需要某种 CASE 语句或 IF。

目前，这贯穿于每个客户，每一次......我想专门为过去 N 天内生日的人运行它。

CREATE TABLE [dbo].[Customers](
    [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [CustomerFirstName] [varchar](30) NOT NULL,
    [CustomerBirthDate] [datetime] NULL,
    [CustomerAge] [smallint] NULL
     CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
)
)

CREATE NONCLUSTERED INDEX [ix_Customers_CustomerBirthDate] ON [dbo].[Customers]
(
    [CustomerBirthDate] ASC
)

INCLUDE([CustomerID],[CustomerFirstName],[CustomerAge])
INSERT INTO [dbo].[Customers] ([CustomerFirstName],[CustomerBirthDate])
VALUES('Stephen', '2000-11-14'),
('Sarah', '1980-11-15'),
('Zoe', '1965-01-01')

基于此，如果我们要运行代码，它会将 CustomerAge 添加到 Stephen 和 Sarah，但保留 Zoe NULL。

任何建议将不胜感激。

亲切问候斯蒂芬

sql-server 函数日期 t-sql where-子句

我认为您上面给出的答案大多是有效的，但是，就我个人而言，我非常不喜欢计算列，无论它们是直接在表定义中定义的，还是通过工作来维护的。我不喜欢前者，因为它们会造成尴尬的索引 - 为了索引计算的列，您需要使其持久化，这意味着每次更新表中的值时都需要重新计算该列，我不喜欢后者，因为它们依赖于作业。

如果你的目标只是让你的年龄计算与你所询问的子句一起工作，我认为这应该为你完成工作，并且也应该是适当的可索引的：WHERE

DECLARE @LastNDays INT = 4

CREATE TABLE #Customers
(
    [CustomerID] [int] IDENTITY PRIMARY KEY,
    [CustomerFirstName] [varchar] (30) NOT NULL,
    [CustomerBirthDate] [datetime] NULL
)

INSERT INTO #Customers ([CustomerFirstName], [CustomerBirthDate])
VALUES
('Stephen', '2000-11-14'),
('Sarah', '1980-11-17'),
('Zoe', '1965-01-01')

SELECT
    CustomerFirstName,
    CustomerBirthDate,
    DATEDIFF(YY, CustomerBirthDate, GETDATE()) + (CASE WHEN DATEADD(YY, DATEDIFF(YY, CustomerBirthDate, GETDATE()), CustomerBirthDate) > GETDATE() THEN -1 ELSE 0 END) AS CustomerAge,
    DATEDIFF(DD, GETDATE(), DATEADD(YY, DATEDIFF(YY, CustomerBirthDate, GETDATE()), CustomerBirthDate)) AS DaysSinceLastBirthDate
FROM #Customers
WHERE DATEDIFF(DD, GETDATE(), DATEADD(YY, DATEDIFF(YY, CustomerBirthDate, GETDATE()), CustomerBirthDate)) BETWEEN -@LastNDays AND 0

DROP TABLE #Customers
``

--Create DDL
CREATE TABLE dbo.Customers (
 customer_id int IDENTITY(1,1)
,birthday datetime
)
CREATE NONCLUSTERED INDEX ncx_Customers_birthday ON dbo.Customers(birthday)

INSERT INTO dbo.Customers (birthday) VALUES
 ('1975-12-15'), ('1980-12-17'), ('1992-04-25'), ('2022-02-05'), ('2013-07-06')
,('1982-12-03'), ('2000-12-04'), ('2002-12-02')

------------------------------------------------
DECLARE @checkdate datetime = '2023-12-05';
DECLARE @yearsAgo tinyint = 150
DECLARE @stopHistory datetime = DATEADD(YEAR, -@yearsAgo, @checkdate);

WITH CTE3priordays AS (
 SELECT @checkdate AS [checkdate]
 UNION ALL
 SELECT DATEADD(DAY, -1, @checkdate) 
 UNION ALL
 SELECT DATEADD(DAY, -2, @checkdate)
 UNION ALL
 SELECT DATEADD(DAY, -3, @checkdate)
)

,CTE_150prioryears AS (
 SELECT DATEADD(YEAR, -1, checkdate) AS [byYears]
   FROM CTE3priordays
 UNION ALL
 SELECT DATEADD(YEAR, -1, byYears) AS [byYears]
   FROM CTE_150prioryears
  WHERE byYears >= @stopHistory
)

SELECT *
  FROM dbo.Customers
 WHERE birthday IN (
--The recursive list of dates
SELECT byYears 
  FROM CTE_150prioryears 
)
OPTION(maxrecursion 700) 
--covering 3 days prior for 150 years produces 601 rows, hence we need maxrecursion

将生成示例输出：

customer_id birthday
----------- ------------------------
6           1982-12-03 00:00:00.000
7           2000-12-04 00:00:00.000
8           2002-12-02 00:00:00.000

这似乎是你所追求的。

上一个：使用period_name的日期范围

下一个：如何以 12 小时 AM/PM 格式显示 JavaScript 日期时间？

在不使用 where 子句中的函数的情况下计算年龄

Calculating Age without using a function in the where clause

评论

评论