SQL【CASE查询视图】

Question

SELECT 
DT, FlowParam, Abs_P, T, Volume, Energy, FlowTime_T,
    (SELECT ' > 1 ' AS Expr1 WHERE (
        (SELECT COUNT(*) AS Expr2
          FROM dbo.BACS_Alarm_1 
          WHERE 
            (DT_T >= dbo.BACS_HourFlow_1.DT_T) AND 
            (DT_T <= dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T)
         ) > 1
     )) AS Something
FROM dbo.BACS_HourFlow_1

如果 WHERE... = 1，我需要 BACS_Alarm_1 表中的输出字段 AS Something 如果 = 0，则输出 Null ...并输出我的消息如果> 1（已经做好了）

我怎样才能实现这个案例？

所以 如果

(SELECT COUNT(*) AS Expr2
  FROM dbo.BACS_Alarm_1 
  WHERE 
    (DT_T >= dbo.BACS_HourFlow_1.DT_T) AND 
    (DT_T <= dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T)
 ) > 1

我输出 ' > 1 ' 作为

if = 1 我从 dbo.BACS_Alarm_1 输出一个字段 if = 0 我输出0或null

Answer 1

保留大部分原样，您可以写：

SELECT 
DT, FlowParam, Abs_P, T, Volume, Energy, FlowTime_T,
    CASE (
        SELECT COUNT(*) AS Expr2
        FROM dbo.BACS_Alarm_1 
        WHERE (DT_T >= dbo.BACS_HourFlow_1.DT_T) 
        AND   (DT_T <= dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T)
     )   WHEN 0 THEN NULL 
         WHEN 1 THEN ' > 1 ' 
         ELSE 'something else'
     END AS Something
FROM dbo.BACS_HourFlow_1

尽管如此，我认为它可以改进一点...我不认为子查询是必要的。既然你提到值 1 和 0 作为可能的结果，我认为你也可以省略 COUNT：

SELECT DT
,      FlowParam
,      Abs_P
,      T
,      Volume
,      Energy
,      FlowTime_T
,      CASE 
           WHEN dbo.BACS_Alarm_1.DT_T IS NULL THEN NULL
           ELSE ' > 1'
       END   AS Something
FROM        dbo.BACS_HourFlow_1
LEFT JOIN   dbo.BACS_Alarm_1 
ON          dbo.BACS_HourFlow_1.DT_T <= dbo.BACS_Alarm_1.DT_T 
AND         dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T >=         dbo.BACS_Alarm_1DT_T

如果确实有更多可能的结果，并且原始中的 COUNT 可能产生超过 1，你可以写它像这样：

SELECT DT
,      FlowParam
,      Abs_P
,      T
,      Volume
,      Energy
,      FlowTime_T
,      CASE COUNT(*) 
           WHEN 0 THEN NULL
           WHEN 1 THEN ' > 1'
           ELSE 'something else'
       END   AS Something
FROM        dbo.BACS_HourFlow_1
LEFT JOIN   dbo.BACS_Alarm_1 
ON          dbo.BACS_HourFlow_1.DT_T <= dbo.BACS_Alarm_1.DT_T 
AND         dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T >= dbo.BACS_Alarm_1DT_T
GROUP BY    BACS_HourFlow_1.DT
,           BACS_HourFlow_1.FlowParam
,           BACS_HourFlow_1.Abs_P
,           BACS_HourFlow_1.T
,           BACS_HourFlow_1.Volume
,           BACS_HourFlow_1.Energy
,           BACS_HourFlow_1.FlowTime_T