SQL Server中批量查询Fault0-Fault10各字段故障状态总时长的方法

Got it, let's figure out how to solve this. You're collecting data in SQL Server where your table has Fault0 through Fault10 columns (each value of 1 means the fault is active), and you need to calculate the total duration each fault was active—all in one query instead of running separate ones for each column. Here are two reliable approaches tailored for SQL Server:

Approach 1: Use UNPIVOT (Clean, Concise) #

This method transforms your wide table (with separate columns for each fault) into a long table, making it easy to aggregate the total duration per fault.

Assuming your table has columns for the start/end time of each state period (adjust the duration calculation to match your actual schema):

SELECT 
    FaultColumn,
    SUM(TotalDuration) AS TotalFaultDuration
FROM (
    SELECT 
        -- List all your fault columns here
        Fault0, Fault1, Fault2, Fault3, Fault4, 
        Fault5, Fault6, Fault7, Fault8, Fault9, Fault10,
        -- Calculate the duration for each row (replace with your actual duration logic)
        DATEDIFF(minute, StartTime, EndTime) AS TotalDuration
    FROM YourFaultTableName
    -- Optional: Filter out rows where all faults are inactive to boost performance
    WHERE Fault0 = 1 OR Fault1 = 1 OR Fault2 = 1 OR 
          Fault3 = 1 OR Fault4 = 1 OR Fault5 = 1 OR 
          Fault6 = 1 OR Fault7 = 1 OR Fault8 = 1 OR 
          Fault9 = 1 OR Fault10 = 1
) AS SourceData
UNPIVOT (
    IsFaultActive FOR FaultColumn IN (
        Fault0, Fault1, Fault2, Fault3, Fault4, 
        Fault5, Fault6, Fault7, Fault8, Fault9, Fault10
    )
) AS UnpivotResults
-- Only include rows where the fault was active
WHERE IsFaultActive = 1
GROUP BY FaultColumn
ORDER BY FaultColumn;

How this works: #

1. The subquery first calculates the duration for each row in your original table and includes all fault columns.
2. UNPIVOT converts each fault column into a row, pairing the fault column name with its active state (0 or 1).
3. We filter for rows where the fault was active (IsFaultActive = 1), then group by the fault column name and sum up the total duration.

Approach 2: Use UNION ALL (Intuitive, Easy to Debug) #

If you prefer a more straightforward approach without using UNPIVOT, you can combine individual per-fault queries with UNION ALL:

SELECT 'Fault0' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault0 = 1
UNION ALL
SELECT 'Fault1' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault1 = 1
UNION ALL
SELECT 'Fault2' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault2 = 1
UNION ALL
SELECT 'Fault3' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault3 = 1
UNION ALL
SELECT 'Fault4' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault4 = 1
UNION ALL
SELECT 'Fault5' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault5 = 1
UNION ALL
SELECT 'Fault6' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault6 = 1
UNION ALL
SELECT 'Fault7' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault7 = 1
UNION ALL
SELECT 'Fault8' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault8 = 1
UNION ALL
SELECT 'Fault9' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault9 = 1
UNION ALL
SELECT 'Fault10' AS FaultColumn, SUM(DATEDIFF(minute, StartTime, EndTime)) AS TotalFaultDuration
FROM YourFaultTableName WHERE Fault10 = 1
ORDER BY FaultColumn;

How this works: #

• Each SELECT statement calculates the total duration for a single fault column.
• UNION ALL combines all these results into a single result set, with each row representing one fault and its total active duration.
• This method is great for debugging, as you can test individual queries easily before combining them.

Key Notes: #

• Replace YourFaultTableName with your actual table name.
• Adjust the DATEDIFF function's time unit (e.g., second, hour, day) to match your desired duration measurement.
• If you already have a pre-calculated duration column in your table, replace the DATEDIFF part with that column name.

内容的提问来源于stack exchange，提问作者M Crawford