根据其他列值计算视图中的列-Calculatecolumninviewbasedonothercolumnvalues

1  

Editted as per @HLGM comments to make it a bit more robust.

根据@HLGM评论编辑,使其更加健壮。

Note that in it's current form, I assume that when

请注意,在它的当前形式中,我假设当

• all alarms equal 1, the range should be NULL

所有报警均等于1,范围应为NULL

• only one alarm equals 0, the range is the value of this alarm.

只有一个警报等于0,范围是此警报的值。

If this does not suffice, OP might clarify what should be returned instead.

如果这还不够,OP可能会澄清应该返回的内容。

SQL Statement

    ;WITH Alarm (C1, C1Alarm, C2, C2Alarm, C3, C3Alarm, C4, C4Alarm) AS (
        SELECT  12.44, 0, 99.43, 0, 4.43, 1, 43.33, 0
        UNION ALL SELECT 12.44, 1, 99.43, 0, 4.43, 1, 43.33, 0
        UNION ALL SELECT 1, 0, 2, 1, 3, 1, 4, 1
        UNION ALL SELECT 1, 1, 2, 1, 3, 1, 4, 1
    )
    , AddRowNumbers AS (
        SELECT  rowNumber = ROW_NUMBER() OVER (ORDER BY C1)
                , C1, C1Alarm
                , C2, C2Alarm
                , C3, C3Alarm
                , C4, C4Alarm
        FROM    Alarm   
    )
    , UnPivotColumns AS (
        SELECT  rowNumber, value = C1 FROM AddRowNumbers WHERE C1Alarm = 0
        UNION ALL SELECT rowNumber, C2 FROM AddRowNumbers WHERE C2Alarm = 0
        UNION ALL SELECT rowNumber, C3 FROM AddRowNumbers WHERE C3Alarm = 0
        UNION ALL SELECT rowNumber, C4 FROM AddRowNumbers WHERE C4Alarm = 0
    )
    SELECT  C1, C1Alarm
            , C2, C2Alarm
            , C3, C3Alarm
            , C4, C4Alarm
            , COALESCE(range1.range, range2.range)
    FROM    AddRowNumbers rowNumber
            LEFT OUTER JOIN (SELECT rowNumber, range = MAX(value) - MIN(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) > 1) range1 ON range1.rowNumber = rowNumber.rowNumber
            LEFT OUTER JOIN (SELECT rowNumber, range = AVG(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) = 1) range2 ON range2.rowNumber = rowNumber.rowNumber  

Test script

;WITH Alarm (C1, C1Alarm, C2, C2Alarm, C3, C3Alarm, C4, C4Alarm) AS (
    SELECT  12.44, 0, 99.43, 0, 4.43, 1, 43.33, 0
    UNION ALL SELECT 12.44, 1, 99.43, 0, 4.43, 1, 43.33, 0
    UNION ALL SELECT 1, 0, 2, 1, 3, 1, 4, 1
    UNION ALL SELECT 1, 1, 2, 1, 3, 1, 4, 1
)
, AddRowNumbers AS (
    SELECT  rowNumber = ROW_NUMBER() OVER (ORDER BY C1)
            , C1, C1Alarm
            , C2, C2Alarm
            , C3, C3Alarm
            , C4, C4Alarm
    FROM    Alarm   
)
, UnPivotColumns AS (
    SELECT  rowNumber, value = C1 FROM AddRowNumbers WHERE C1Alarm = 0
    UNION ALL SELECT rowNumber, C2 FROM AddRowNumbers WHERE C2Alarm = 0
    UNION ALL SELECT rowNumber, C3 FROM AddRowNumbers WHERE C3Alarm = 0
    UNION ALL SELECT rowNumber, C4 FROM AddRowNumbers WHERE C4Alarm = 0
)
SELECT  C1, C1Alarm
        , C2, C2Alarm
        , C3, C3Alarm
        , C4, C4Alarm
        , COALESCE(range1.range, range2.range)
FROM    AddRowNumbers rowNumber
        LEFT OUTER JOIN (SELECT rowNumber, range = MAX(value) - MIN(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) > 1) range1 ON range1.rowNumber = rowNumber.rowNumber
        LEFT OUTER JOIN (SELECT rowNumber, range = AVG(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) = 1) range2 ON range2.rowNumber = rowNumber.rowNumber  

1  

The OP's comment "Even if I had 200 columns..." leads me to believe that similar functionality will be needed in multiple places. So I would create a function that accepts a "Reading" column and an "Alarm" bit, and returns NULL when Alarm is set. This allows one to take advantage of the way NULL values are treated by MIN and MAX.

OP的评论“即使我有200列......”也让我相信在多个地方都需要类似的功能。所以我会创建一个接受“读取”列和“报警”位的函数,并在设置警报时返回NULL。这允许人们利用MIN和MAX处理NULL值的方式。

CREATE FUNCTION UnalarmedReading
(
    @Value float,
    @Alarm bit
)
RETURNS float
AS
BEGIN
return case when @Alarm=1 then null else @Value end
END

Here is some test data:

这是一些测试数据:

create table Readings (
    KeyColumn int not null,
    Column1 float,
    Column1Alarm bit,
    Column2 float,
    Column2Alarm bit,
    Column3 float,
    Column3Alarm bit,
    Column4 float,
    Column4Alarm bit,
)

insert into Readings(
    KeyColumn,
    Column1,Column1Alarm,
    Column2,Column2Alarm,
    Column3,Column3Alarm,
    Column4,Column4Alarm
) values (
    1,
    12.44, 0,
    99.43, 0,
    4.43, 1,
    43.33, 0
)

insert into Readings(
    KeyColumn,
    Column1,Column1Alarm,
    Column2,Column2Alarm,
    Column3,Column3Alarm,
    Column4,Column4Alarm
) values (
    2,
    124.4, 0,
    994.3, 0,
    44.3, 1,
    433.3, 0
)

And to use MIN and MAX you unpivot:

要使用MIN和MAX,您可以忽略:

;with NonAlarmReadings as (
    select KeyColumn, dbo.UnalarmedReading(Column1, Column1Alarm) as C1,
        dbo.UnalarmedReading(Column2, Column2Alarm) as C2,
        dbo.UnalarmedReading(Column3, Column3Alarm) as C3,
        dbo.UnalarmedReading(Column4, Column4Alarm) as C4
    from Readings
),
Normalized as (
    select *
    from NonAlarmReadings
    unpivot (Reading for BaseColumn in (C1, C2, C3, C4)) as upvt
)
select KeyColumn, min(Reading) as MinReading, max(Reading) as MaxReading,
    abs(min(Reading) - max(Reading)) as ReadingRange
from Normalized
group by KeyColumn