SQL Server双表匹配按指定状态顺序输出统计结果的实现问题

Let's break down your problem step by step and fix the SQL error while meeting all your requirements.

Error Explanation #

The error you're seeing happens because you're referencing P.STATUS in your SELECT clause, but it's neither wrapped in an aggregate function nor included in your GROUP BY clause. Since your original query groups by CITYID and JOBNAME (via ROLLUP), the database can't determine which STATUS value to return for each group—this violates SQL's grouping rules.

Solution Approach #

To meet your full set of requirements, we'll structure the query to:

1. Explicitly define the status order ('A' first, then 'E') to enforce the required output sequence
2. Ensure all job names from the JOB table are included (even those with no matching employees in EMP)
3. Calculate counts and average salaries per job-status combination, replacing NULLs with 0 where no data exists
4. Add an 'X' flag for non-zero records
5. Generate total rows for each status using grouping sets

Corrected SQL Query #

-- Define target parameters (easily adjustable for different inputs)
DECLARE @TargetCityID SMALLINT = 10;
DECLARE @TargetYear SMALLINT = 2015;

WITH StatusList AS (
    -- Fix status order: 'A' first, then 'E'
    SELECT 'A' AS STATUS UNION ALL SELECT 'E'
),
BaseDataset AS (
    -- Create base rows for every job + status combination for the target city/year
    SELECT
        @TargetCityID AS CITYID,
        @TargetYear AS YEAR,
        SL.STATUS,
        J.JOBID,
        J.JOBNAME
    FROM JOB J
    CROSS JOIN StatusList SL
)
SELECT
    BD.CITYID,
    CASE BD.STATUS WHEN 'A' THEN 'STATUS-A' ELSE 'STATUS-E' END AS STATUS_TYPE,
    -- Show 'TOTAL' for aggregated rows
    CASE WHEN GROUPING(BD.JOBNAME) = 1 THEN 'TOTAL' ELSE BD.JOBNAME END AS JOBNAME,
    -- Count of employees for this job-status pair
    COUNT(E.EMPID) AS COUNT,
    -- Average monthly salary (divided by 12 as in your original query), default to 0
    COALESCE(AVG(CAST(E.SAL AS DECIMAL(13,2)))/12, 0) AS [AVG SAL],
    -- Add 'X' flag for non-zero records
    CASE WHEN COUNT(E.EMPID) > 0 OR COALESCE(AVG(CAST(E.SAL AS DECIMAL(13,2)))/12, 0) > 0 THEN 'X' ELSE '' END AS FLAG
FROM BaseDataset BD
LEFT JOIN EMP E
    ON BD.JOBID = E.JOBID
    AND BD.CITYID = E.CITYID
    AND BD.YEAR = E.YEAR
    AND BD.STATUS = E.STATUS
GROUP BY
    BD.CITYID,
    BD.STATUS,
    GROUPING SETS (
        (BD.JOBNAME),  -- Detailed rows per job
        ()             -- Total row per status
    )
ORDER BY
    BD.STATUS,  -- Keep 'A' records before 'E'
    GROUPING(BD.JOBNAME),  -- Place total rows after detailed rows for each status
    BD.JOBNAME;

Key Fixes & Features #

• Resolved the GROUP BY Error: We now group by STATUS (from our StatusList CTE), which is part of the base dataset, so the database can correctly associate status values with each group.
• All Jobs Included: The cross join between JOB and StatusList ensures every job name appears for both statuses—jobs with no matching employees will show 0 for count and average salary.
• Enforced Status Order: The StatusList CTE and final ORDER BY clause guarantee 'STATUS-A' records appear before 'STATUS-E'.
• Non-Zero Flag: The FLAG column adds an 'X' for any row where either the count or average salary is non-zero.
• Total Rows: GROUPING SETS generates both individual job rows and a total row for each status. The GROUPING() function identifies aggregated rows to display 'TOTAL' as the job name.
• Parameterization: Target city and year are stored as variables, making it simple to adjust the query for different inputs.

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