如何将多段SQL查询合并为按DetectorID单条返回的结果

Let's fix this by restructuring your queries into Common Table Expressions (CTEs) so we can join all the aggregated results together, ensuring each DetectorID gets exactly one row with all required metrics. The key here is that each CTE groups by DetectorID (so one row per detector), then we left-join them to the main stats query to preserve all detectors from your initial result set.

Here's the revised SQL:

DECLARE @StartDate datetime SET @StartDate = '2017-12-01 00:00'
DECLARE @EndDate datetime SET @EndDate = '2018-01-01 00:00'
DECLARE @Limit1 real SET @Limit1 = 200;
DECLARE @AssenLimit1 real SET @AssenLimit1 = 0;
DECLARE @Limit2 real SET @Limit2 = 300;
DECLARE @AssenLimit2 real SET @AssenLimit2 = 0;
DECLARE @Limit3 real SET @Limit3 = 350;
DECLARE @AssenLimit3 real SET @AssenLimit3 = 0;
DECLARE @Limit4 real SET @Limit4 = 400;
DECLARE @AssenLimit4 real SET @AssenLimit4 = 0;
DECLARE @Limit5 real SET @Limit5 = 500;
DECLARE @AssenLimit5 real SET @AssenLimit5 = 0;

-- CTE 1: Main wheel damage stats (your first query)
WITH MainStats AS (
    SELECT 
        DetectorID, 
        AVG(ValidWIM) AS PI_mean_WIM, 
        COUNT(ValidWIM) AS PI_mean_WIM2, 
        COUNT(CASE WHEN ValidWIM > 0.5 THEN ValidWIM ELSE NULL END) AS PI_mean_WIM3,
        AVG((ValidWDD_Left+ValidWDD_Right)/2) AS PI_mean_WDD,
        COUNT(ValidWDD_Left) AS PI_mean_WDD2,
        COUNT(CASE WHEN (ValidWDD_Left>0.5 AND ValidWDD_Right>0.5) THEN ValidWDD_Left ELSE NULL END) AS PI_mean_WDD3,
        COUNT(CASE WHEN AxleLoad IS NOT NULL Then TrainPassageInformationID ELSE NULL END) AS nWheels,
        COUNT(CASE WHEN (PeakForceLeft > @Limit1 OR PeakForceRight>@Limit1) THEN TrainPassageInformationID ELSE NULL END) AS [n>200],
        COUNT(CASE WHEN (PeakForceLeft > @Limit2 OR PeakForceRight>@Limit2) THEN TrainPassageInformationID ELSE NULL END) AS [n>300],
        COUNT(CASE WHEN (PeakForceLeft > @Limit3 OR PeakForceRight>@Limit3) THEN TrainPassageInformationID ELSE NULL END) AS [n>350],
        COUNT(CASE WHEN (PeakForceLeft > @Limit4 OR PeakForceRight>@Limit4) THEN TrainPassageInformationID ELSE NULL END) AS [n>400],
        COUNT(CASE WHEN (PeakForceLeft > @Limit5 OR PeakForceRight>@Limit5) THEN TrainPassageInformationID ELSE NULL END) AS [n>500]
    FROM WheelDamage 
    WHERE (TimeOfAxle > @StartDate AND TimeOfAxle < @EndDate) 
      AND DetectorID IN (11,12)
    GROUP BY DetectorID
),
-- CTE 2: Left noise stats (your second query)
NoiseLeftStats AS (
    SELECT 
        DetectorID, 
        AVG(PeakForceLeft) AS NoiseLeft
    FROM WheelDamage as t 
    WHERE PeakForceLeft IN (
        SELECT TOP 10 PERCENT PeakForceLeft 
        FROM wheeldamage as tt 
        WHERE tt.WheelDamageID = t.WheelDamageID 
          AND tt.PeakForceLeft <> 0 
          AND tt.PeakForceLeft IS NOT NULL 
          AND (t.TimeOfAxle > @StartDate AND t.TimeOfAxle < @EndDate) 
        ORDER BY tt.PeakForceLeft asc
    )
    GROUP BY DetectorID
),
-- CTE 3: Right noise stats (your third query)
NoiseRightStats AS (
    SELECT 
        DetectorID, 
        AVG(PeakForceRight) AS NoiseRight
    FROM WheelDamage as t 
    WHERE PeakForceLeft IN (
        SELECT TOP 10 PERCENT PeakForceRight 
        FROM wheeldamage as tt 
        WHERE tt.WheelDamageID = t.WheelDamageID 
          AND tt.PeakForceRight <> 0 
          AND tt.PeakForceLeft IS NOT NULL 
          AND (t.TimeOfAxle > @StartDate AND t.TimeOfAxle < @EndDate) 
        ORDER BY tt.PeakForceLeft asc
    )
    GROUP BY DetectorID
),
-- CTE 4: Tag passage stats (your fourth query)
TagsStats AS (
    SELECT 
        DetectorId, 
        COUNT(DateTime) AS Tags1
    FROM [TagPassage] 
    WHERE Valid = 1 
      AND (DateTime > @StartDate AND DateTime < @EndDate)
    GROUP by DetectorID
),
-- CTE 5: Train passage stats (your fifth query)
TrainStats AS (
    SELECT 
        DetectorID, 
        COUNT(CASE WHEN (TotalWeight=0 OR TotalWeight IS NULL) AND (HasTrainStandStill<>1 OR HasTrainStandStill IS NULL) THEN TrainPassageInformationID ELSE NULL END) AS notAnalyzed,
        SUM(TotalWeight) AS TotalWeight
    FROM TrainPassageInformation 
    WHERE (Datetime > @StartDate AND Datetime < @EndDate)
    GROUP By DetectorID
)
-- Join all CTEs together to get one row per DetectorID
SELECT 
    ms.DetectorID,
    ms.PI_mean_WIM,
    ms.PI_mean_WIM2,
    ms.PI_mean_WIM3,
    ms.PI_mean_WDD,
    ms.PI_mean_WDD2,
    ms.PI_mean_WDD3,
    ms.nWheels,
    ms.[n>200],
    ms.[n>300],
    ms.[n>350],
    ms.[n>400],
    ms.[n>500],
    nls.NoiseLeft,
    nrs.NoiseRight,
    ts.Tags1,
    trs.notAnalyzed,
    trs.TotalWeight
FROM MainStats ms
LEFT JOIN NoiseLeftStats nls ON ms.DetectorID = nls.DetectorID
LEFT JOIN NoiseRightStats nrs ON ms.DetectorID = nrs.DetectorID
LEFT JOIN TagsStats ts ON ms.DetectorID = ts.DetectorID
LEFT JOIN TrainStats trs ON ms.DetectorID = trs.DetectorID
ORDER BY ms.DetectorID ASC;

Key Fixes & Notes: #

• CTE Structure: Breaking each aggregate query into a CTE makes the code easier to read and maintain, and ensures each CTE returns one row per DetectorID (so no "subquery returned more than one row" errors when joining).
• LEFT JOIN: Using LEFT JOIN ensures we keep all detectors from your original MainStats query, even if they have no matching rows in the other tables (those columns will just show NULL).
• Bracket Handling: Wrapped column names like [n>200] in square brackets to avoid syntax issues with special characters.

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