时间戳

首页 > 解决方案 > SQL Server 将 2 个条件合二为一

sql-server - SQL Server 将 2 个条件合二为一

问题描述

我有以下 SQL 语句来获取 2020 年和 2021 年的约会：

SELECT
    YEAR(A.AppointmentDate) AS [Year], 
    ATS.[Description] AS AppointmentSource, 
    MONTH(A.Appointmentdate) AS MonthNumber, 
    COUNT(A.AppointmentID) AS NumberOfAppointments
FROM 
    Appointment A
INNER JOIN
    AppointmentStatus AST ON AST.AppointmentStatusID = A.AppointmentStatusID
INNER JOIN
    AppointmentSource ATS ON ATS.AppointmentSourceID = A.AppointmentSourceID
WHERE 
    YEAR(A.AppointmentDate) IN (2021, 2020)
    AND ATS.AppointmentSourceID IN (1, 3)
    AND A.AppointmentStatusID = 1 -- Active
GROUP BY 
    YEAR(A.AppointmentDate), MONTH(A.AppointmentDate), 
    ATS.[Description], AST.AppointmentStatusName

约会来源表 (AppointmentSource) 有以下几行：

  1. 呼叫中心
  2. 代理人
  3. 网络

我需要将呼叫中心 + 代理的数量显示为“呼叫中心”，将网络显示为“网络”

请帮我。

标签： sql-servergroup-by

解决方案

这是一种解决方法，但如果我正确理解您的问题，应该会得到您所追求的：

SELECT
    YEAR(A.AppointmentDate) AS [Year], 
    CASE WHEN ATS.AppointmentSourceID in (1,2) THEN ' Call Center' ELSE 'Web' END  AS AppointmentSource, 
    MONTH(A.Appointmentdate) AS MonthNumber, 
   COUNT(A.AppointmentID) AS NumberOfAppointments
FROM 
    Appointment A
INNER JOIN
    AppointmentStatus AST ON AST.AppointmentStatusID = A.AppointmentStatusID
INNER JOIN
    AppointmentSource ATS ON ATS.AppointmentSourceID = A.AppointmentSourceID
WHERE 
    YEAR(A.AppointmentDate) IN (2021, 2020)
    --AND ATS.AppointmentSourceID IN (1, 3)
    AND A.AppointmentStatusID = 1 -- Active
GROUP BY 
    YEAR(A.AppointmentDate), MONTH(A.AppointmentDate), 
    CASE WHEN ATS.AppointmentSourceID in (1,2) THEN ' Call Center' ELSE 'Web' END

基本上，我们在这里忽略了查找表，并将值硬编码在 case 语句中，以将两个约会源组合为一个。

如果您有太多查找值，则替代选项是连接条件中的另一种解决方法：

SELECT
    YEAR(A.AppointmentDate) AS [Year], 
    ATS.[Description] AS AppointmentSource, 
    MONTH(A.Appointmentdate) AS MonthNumber, 
   COUNT(A.AppointmentID) AS NumberOfAppointments
FROM 
    Appointment A
INNER JOIN
    AppointmentStatus AST ON AST.AppointmentStatusID = CASE when A.AppointmentStatusID in (1,2) then 1 else A.AppointmentStatusID end --checking if AppointmentStatusID is 1 or 2 then I am passing 1 to the join condition
INNER JOIN
    AppointmentSource ATS ON ATS.AppointmentSourceID = A.AppointmentSourceID
WHERE 
    YEAR(A.AppointmentDate) IN (2021, 2020)
    --AND ATS.AppointmentSourceID IN (1, 3)
    AND A.AppointmentStatusID = 1 -- Active
GROUP BY 
    YEAR(A.AppointmentDate), MONTH(A.AppointmentDate), 
    ATS.[Description], AST.AppointmentStatusName

推荐阅读