SQL IF或搜索语句

Question

所有人，

我可能想得太多了，或者只是SQL不太好。我正在使用Windows sql server 2016，并试图创建一个if语句，作为R函数中搜索字段的一部分。下面是我的sql代码，我正在尝试做一个if语句，在该语句中，我在一个选择字段中搜索一个数字，如果不匹配，只列出该列中的所有数字。

SELECT
df.[Name] AS Farm_Name
, dw.[Name] AS WTG_Name
, dw.[Id] AS WTG_Id     
, fa.[StartTime] AS StartTime
, fa.[EndTime] AS EndTime
, fa.[LocalStartTime] AS LocalStartTime
, fa.[LocalEndTime] AS LocalEndTime
, da.[Code] AS Alarm_Code
, da.[Description] AS Alarm_Description
, fa.[SeverityStart] AS Severity_Start
, fa.[MaintenanceFlag] AS Maintenance_Flag
, da.[Stop] AS Alarm_Stop
, da.[Warning] AS Alarm_Warning
, da.[Maintenance] AS Alarm_Maintenance
, da.[LBA] AS Alarm_LBA    
FROM [DataMart].[FactAlarms10Min] AS fa
INNER JOIN [DataMart].[DimAlarm] AS da
ON fa.[IdAlarm] = da.[Id]
INNER JOIN [DataMart].[DimWTG] AS dw
ON fa.[IdWTG] = dw.[Id]
INNER JOIN [DataMart].[DimFarm] AS df
ON dw.[IdFarm] = df.[Id]
WHERE dw.[Name] IN ('SC4020') -- WTG Name/Number 
IF da.[Code] = 707
THEN da.[Code] = 707;    
ELSE  da.[Code] > 0);
AND fa.[StartTime] BETWEEN (convert(datetime, '2021-01-01')) AND (convert(datetime, '2021-01-10'))

所以这就是我要做的，搜索da.[Code]编号707，如果没有匹配，只显示da.[Code]中的所有内容。此字段da.[Code]将是0-1000 之间的数字

Answer 1

如果您只选择具有da的行。[Code]=707，或者如果不是所有具有da的行都选择[Code]>0

那么你应该使用

SELECT
df.[Name] AS Farm_Name
, dw.[Name] AS WTG_Name
, dw.[Id] AS WTG_Id     
, fa.[StartTime] AS StartTime
, fa.[EndTime] AS EndTime
, fa.[LocalStartTime] AS LocalStartTime
, fa.[LocalEndTime] AS LocalEndTime
, da.[Code] AS Alarm_Code
, da.[Description] AS Alarm_Description
, fa.[SeverityStart] AS Severity_Start
, fa.[MaintenanceFlag] AS Maintenance_Flag
, da.[Stop] AS Alarm_Stop
, da.[Warning] AS Alarm_Warning
, da.[Maintenance] AS Alarm_Maintenance
, da.[LBA] AS Alarm_LBA 
FROM [DataMart].[FactAlarms10Min] AS fa
INNER JOIN [DataMart].[DimAlarm] AS da
ON fa.[IdAlarm] = da.[Id]
INNER JOIN [DataMart].[DimWTG] AS dw
ON fa.[IdWTG] = dw.[Id]
INNER JOIN [DataMart].[DimFarm] AS df
ON dw.[IdFarm] = df.[Id]
WHERE dw.[Name] IN ('SC4020') -- WTG Name/Number 
AND ( da.[Code] = 707 OR  da.[Code] > 0)
AND fa.[StartTime] BETWEEN (convert(datetime, '2021-01-01')) AND (convert(datetime, '2021-01-10'))

Answer 2

您可以使用CASE语句切换IF，使用Larnu注释中的EXISTS逻辑：

; WITH Alarms
AS (
SELECT
df.[Name] AS Farm_Name
, dw.[Name] AS WTG_Name
, dw.[Id] AS WTG_Id     
, fa.[StartTime] AS StartTime
, fa.[EndTime] AS EndTime
, fa.[LocalStartTime] AS LocalStartTime
, fa.[LocalEndTime] AS LocalEndTime
, da.[Code] AS Alarm_Code
, da.[Description] AS Alarm_Description
, fa.[SeverityStart] AS Severity_Start
, fa.[MaintenanceFlag] AS Maintenance_Flag
, da.[Stop] AS Alarm_Stop
, da.[Warning] AS Alarm_Warning
, da.[Maintenance] AS Alarm_Maintenance
, da.[LBA] AS Alarm_LBA    
FROM [DataMart].[FactAlarms10Min] AS fa
INNER JOIN [DataMart].[DimAlarm] AS da
ON fa.[IdAlarm] = da.[Id]
INNER JOIN [DataMart].[DimWTG] AS dw
ON fa.[IdWTG] = dw.[Id]
INNER JOIN [DataMart].[DimFarm] AS df
ON dw.[IdFarm] = df.[Id]
WHERE dw.[Name] IN ('SC4020') -- WTG Name/Number 
AND fa.[StartTime] BETWEEN (convert(datetime, '2021-01-01')) AND (convert(datetime, '2021-01-10'))
)
SELECT *
FROM Alarms
WHERE Alarm_Code = 
CASE WHEN EXISTS(SELECT NULL FROM Alarms WHERE Alarm_Code = 707) 
THEN 707 
ELSE Alarm_Code 
END ;

这里我们要求707的Alarm_Code，如果存在的话，否则给我Alarm_Code=Alarm_Code的一切，这就是一切。

Answer 3

在where子句中，您可以简单地用case编写条件，如下所示：(da.[Code]=707时的情况，然后707其他da.Code结束)=da.[Code]如果da.code＝707，那么其中子句将是707＝da.code，否则它将是da.code=da.code这对于da.code的所有值都是真的。

SELECT
df.[Name] AS Farm_Name
, dw.[Name] AS WTG_Name
, dw.[Id] AS WTG_Id     
, fa.[StartTime] AS StartTime
, fa.[EndTime] AS EndTime
, fa.[LocalStartTime] AS LocalStartTime
, fa.[LocalEndTime] AS LocalEndTime
, da.[Code] AS Alarm_Code
, da.[Description] AS Alarm_Description
, fa.[SeverityStart] AS Severity_Start
, fa.[MaintenanceFlag] AS Maintenance_Flag
, da.[Stop] AS Alarm_Stop
, da.[Warning] AS Alarm_Warning
, da.[Maintenance] AS Alarm_Maintenance
, da.[LBA] AS Alarm_LBA 
FROM [DataMart].[FactAlarms10Min] AS fa
INNER JOIN [DataMart].[DimAlarm] AS da
ON fa.[IdAlarm] = da.[Id]
INNER JOIN [DataMart].[DimWTG] AS dw
ON fa.[IdWTG] = dw.[Id]
INNER JOIN [DataMart].[DimFarm] AS df
ON dw.[IdFarm] = df.[Id]
WHERE dw.[Name] IN ('SC4020') -- WTG Name/Number 
and (case when da.[Code] = 707 then 707 else da.code end) = da.[Code] 
AND fa.[StartTime] BETWEEN (convert(datetime, '2021-01-01')) AND (convert(datetime, '2021-01-10'))