SQL [案例查询视图]

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

我需要从BACS_Alarm_1表中输出字段AS Something if WHERE... = 1和output Null if =0 ...and output my message if >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 I输出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