SQL子查询返回多行值报错，关联查询时遇问题求助

Hey there, let's tackle that error you're running into. I've parsed your query and pinpointed exactly why this is happening, plus have two straightforward solutions to get your query working as expected.

What's Causing the Error? #

The problem lies in the nested subquery you're using to calculate Nxt_date. Specifically, this part:

(SELECT COUNT(1) FROM Holiday_list WHERE Date_Fmt BETWEEN School_update AND dt)

This subquery isn't tied to the specific row from Application_Status you're processing in the outer query. Without that explicit link, SQL Server can't tell that you want the holiday count for this specific row's date range—instead, it might return multiple count values (one for every matching row in Holiday_list), which violates the rule that subqueries used as expressions (like in a + operation) must return exactly one value.

Solution 1: Fix the Correlated Subquery #

We can rewrite the Nxt_date calculation to explicitly reference the outer Application_Status row, ensuring the holiday count subquery only returns one value per row. Here's the updated full query:

SELECT 
    b.Service_Name, 
    c.Service_Type, 
    a.Application_No, 
    a.Reg_No, 
    a.Student_Name, 
    CONVERT(char(10), 
        -- Calculate the base target date first
        CASE WHEN a.Service_TypeID = '1' THEN (a.School_update + 30) ELSE (a.School_update + 5) END
        -- Add holiday count, explicitly tied to the current row's dates
        + (SELECT COUNT(1) 
           FROM Holiday_list h 
           WHERE h.Date_Fmt BETWEEN a.School_update AND (CASE WHEN a.Service_TypeID = '1' THEN (a.School_update + 30) ELSE (a.School_update + 5) END)), 
        103) AS Nxt_date,
    DATEDIFF(DAY, a.School_update, GETDATE()) AS Day_Count, 
    a.Created_Date, 
    a.School_Code, 
    CASE WHEN a.Payment_Status = 'Y' THEN 'PAID' WHEN a.Payment_Status = 'N' THEN 'NOT PAID' END AS Payment_Status 
FROM Application_Status a
JOIN MST_Service b ON a.Service_ID = b.Service_ID
JOIN MST_ServiceType c ON a.Service_TypeID = c.Type_ID
JOIN KSEEBMASTERS.dbo.MST_SCHOOL s ON s.SCM_SCHOOL_CODE COLLATE Latin1_General_CI_AI = a.School_Code
JOIN MST_Division d ON s.DIST_CODE COLLATE Latin1_General_CI_AI = d.DistrictCode
WHERE d.DivisionCode = 'ED' 
  AND a.Payment_Status = 'Y' 
  AND a.school_status = 'Y' 
  AND a.Div_Status = 'N';

Notice how we're using a.School_update and explicitly calculating the target date within the subquery—this tells SQL Server to only count holidays for the current row's date range, so it returns a single number every time.

Solution 2: Use a CTE for Cleaner Code #

If you prefer more readable, maintainable code, a Common Table Expression (CTE) can separate the date/holiday calculation logic from the main query:

WITH ApplicationDateCalculations AS (
    SELECT 
        Application_No,
        Reg_No,
        Student_Name,
        School_update,
        Service_TypeID,
        Payment_Status,
        school_status,
        Div_Status,
        Created_Date,
        School_Code,
        Service_ID,
        -- Compute base target date
        CASE WHEN Service_TypeID = '1' THEN (School_update + 30) ELSE (School_update + 5) END AS TargetDate,
        -- Compute holiday count for this row's date range
        (SELECT COUNT(1) 
         FROM Holiday_list h 
         WHERE h.Date_Fmt BETWEEN School_update AND (CASE WHEN Service_TypeID = '1' THEN (School_update + 30) ELSE (School_update + 5) END)) AS HolidayCount
    FROM Application_Status
)
SELECT 
    b.Service_Name, 
    c.Service_Type, 
    adc.Application_No, 
    adc.Reg_No, 
    adc.Student_Name, 
    CONVERT(char(10), adc.TargetDate + adc.HolidayCount, 103) AS Nxt_date,
    DATEDIFF(DAY, adc.School_update, GETDATE()) AS Day_Count, 
    adc.Created_Date, 
    adc.School_Code, 
    CASE WHEN adc.Payment_Status = 'Y' THEN 'PAID' WHEN adc.Payment_Status = 'N' THEN 'NOT PAID' END AS Payment_Status 
FROM ApplicationDateCalculations adc
JOIN MST_Service b ON adc.Service_ID = b.Service_ID
JOIN MST_ServiceType c ON adc.Service_TypeID = c.Type_ID
JOIN KSEEBMASTERS.dbo.MST_SCHOOL s ON s.SCM_SCHOOL_CODE COLLATE Latin1_General_CI_AI = adc.School_Code
JOIN MST_Division d ON s.DIST_CODE COLLATE Latin1_General_CI_AI = d.DistrictCode
WHERE d.DivisionCode = 'ED' 
  AND adc.Payment_Status = 'Y' 
  AND adc.school_status = 'Y' 
  AND adc.Div_Status = 'N';

This way, all the date-specific logic lives in the CTE, making the main query much easier to read and modify later.

Quick Optimization Tip #

If your Holiday_list table has a lot of rows, adding an index on the Date_Fmt column will speed up the holiday count subquery significantly—this is especially helpful if you're running this query frequently.

内容的提问来源于stack exchange，提问作者KriShna RaJendra N PraSad