mysql - 日期时间子句的 SQL 大小写帮助

Question

我正在运行此查询:

SELECT
    sm.UPDATE_DATE
FROM
    EAT_Tickets
    LEFT JOIN
        MAS_Users
        ON EAT_Tickets.tic_assignedTo = MAS_Users.use_userKey
    INNER JOIN
        EAT_TicketStatusesLK
        ON EAT_Tickets.tic_ticketStatusKey = EAT_TicketStatusesLK.tis_ticketStatusKey
    INNER JOIN
        [HSHPSQL03].[ServiceManager].dbo.[OCMLM1] sm
        ON sm.NUMBER = EAT_Tickets.tic_oarID
WHERE
        sm.UCD_Status <> 'Closed'
    AND sm.UCD_Status <> 'denied'
    AND sm.NUMBER = EAT_Tickets.tic_oarID
    AND sm.UCD_BRIEF_DESC NOT LIKE '%Access termination%'
    AND MAS_Users.use_ADLogin = 'User1'

它显然返回日期时间，例如2015-06-15 06:48:12:000

但是，我想添加某种大小写，因为我想查看 3 天内有多少是最新的。例如，如果我有 5 个结果，其中 4 个在今天日期的 3 天内，我希望结果集显示“4”。我不需要查看日期/时间。我希望外壳能够比较 3 天内的日期并添加到计数中。这可能吗？

Answer 1

我认为这应该适合你。该总和将把 3 天内的任何日期相加。

SELECT
  SUM(CASE
    WHEN DATEDIFF(dd, sm.UPDATE_DATE, GETDATE()) <= 3 THEN 1
    ELSE 0
  END) 'DaysWithin3'
FROM EAT_Tickets
LEFT JOIN MAS_Users
  ON EAT_Tickets.tic_assignedTo = MAS_Users.use_userKey
INNER JOIN EAT_TicketStatusesLK
  ON EAT_Tickets.tic_ticketStatusKey = EAT_TicketStatusesLK.tis_ticketStatusKey
INNER JOIN [HSHPSQL03].[ServiceManager].dbo.[OCMLM1] sm
  ON sm.NUMBER = EAT_Tickets.tic_oarID
WHERE sm.UCD_Status <> 'Closed'
AND sm.UCD_Status <> 'denied'
AND sm.NUMBER = EAT_Tickets.tic_oarID
AND sm.UCD_BRIEF_DESC NOT LIKE '%Access termination%'
AND MAS_Users.use_ADLogin = 'User1'