SQL 服务器 : matching two tables and results in the order of proper column

Question

我有两个表 JOB 和 EMP；结构和值是这样的:

CREATE TABLE JOB 
(
    JOBID SMALLINT UNIQUE NOT NULL,
    JOBNAME CHAR(15)
);

CREATE TABLE EMP
(
    EMPID SMALLINT, 
    JOBID SMALLINT, 
    SAL SMALLINT, 
    CITYID SMALLINT,
    YEAR SMALLINT,
    STATUS CHAR(1)
);

INSERT INTO JOB(JOBID, JOBNAME) 
VALUES (1, 'DEVELOPMENT'),
       (2, 'DEVELOPMENT'),
       (3, 'TESTING'),
       (4, 'TESTING'),
       (7, 'TESTING'),
       (9, 'RESEARCH'),
       (8, 'HR');

INSERT INTO EMP (EMPID , JOBID, SAL, CITYID, YEAR, STATUS) 
VALUES (100, 1, 1000, 10, 2015, 'A'),
       (200, 2, 2000, 10, 2015, 'A'),
       (300, 1, 2500, 20, 2015, 'A'),
       (400, 3, 1000, 10, 2016, 'A'),
       (500, 6, 3000, 10, 2015, 'E'),
       (600, 8, 1000, 30, 2015, 'A'),
       (700, 8, 2000, 10, 2015, 'E'),
       (800, 9, 1500, 10, 2015, 'A');

我想显示所有职位名称和平均工资；对于jobname，如果jobid不存在则显示0

对于给定的输入 cityid 、YEAR 和 STATUS (Emp 表)，取所有 每个 jobname 的 jobid(来自 job 表)并在 Emp 表中匹配，如果存在则显示 count(Emp 表中存在的 jobid 的计数)和 avgsal else 0 对于 count 和 avgsal。而 Sal 是根据 Status 计算的。如果 Status 是 'A' Sal 转到 Status-A-Sal else Status-E-Sal。对于每个匹配的即非零记录，将“X”放在另一个字段中

对于 Cityid 的第 10 年和第 20 年 2015，输出应该是这样的。应该首先显示 Status 'A' 的结果，然后是 Status“E”。在结果中添加了状态类型字段。

Cityid  Status-type jobname         count   STATUS      sal
--------------------------------------------------------------
10      STATUSA     development     2       X           1500
10      STATUSA     TESTING         0                   0
10      STATUSA     RESEARCH        1       X           1500 
10      STATUSA     HR              0                   0
10      total                       3                   0
10      STATUSE     development     0                   0
10      STATUSE     TESTING         0                   0
10      STATUSE     RESEARCH        0                   0 
10      STATUSE     HR              1        X          2000
10      total                       1                   2000

20      STATUSA     development     1        X          2500
20      STATUSA     TESTING         0                   0
20      STATUSA     RESEARCH        0                   0 
20      STATUSA     HR              0                   0
20      total                       1                   2500
20      STATUSE     development     0                   0
20      STATUSE     TESTING         0                   0
20      STATUSE     RESEARCH        0                   0 
20      STATUSE     HR              0                   0
20      total                       0                   0

如何让结果一个接一个的状态？

我试过这样但是它扔了

SELECT C.CITYID  AS CITYID,
           CASE WHEN P.STATUS ='A' THEN 'STATUSA' ELSE 'STATUSE' END AS STATUS_TYPE ,
           COALESCE(J.JOBNAME, 'TOTAL') AS JOBNAME,
           COUNT(CASE WHEN P.STATUS ='A' THEN P.CITYID END ) AS COUNT ,
           COALESCE(AVG(CAST(CASE WHEN P.STATUS = 'A' THEN P.SAL END AS DECIMAL(13,2)))/12 , 0) AS "AVG SAL",
           COUNT(CASE WHEN P.STATUS ='E' THEN P.CITYID END ) AS  COUNT  ,
           COALESCE(AVG(CAST(CASE WHEN P.STATUS = 'E' THEN P.SAL END AS DECIMAL(13,2)))/12 , 0) AS "AVG SAL"
    FROM JOB1 J
    CROSS JOIN 
            (SELECT DISTINCT CITYID 
                FROM EMP1  B WHERE CITYID = 10

            ) C
    LEFT JOIN EMP1 P ON P.JOBID = J.JOBID 
            AND  P.CITYID = C.CITYID and
            YEAR = 2015
        GROUP BY ROLLUP(C.CITYID,  J.JOBNAME );

ERROR: Column 'EMP1.STATUS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

IF OBJECT_ID('tempdb..#JOB') IS NOT NULL
    DROP TABLE #JOB

CREATE TABLE #JOB 
(
    JOBID SMALLINT UNIQUE NOT NULL,
    JOBNAME CHAR(15)
);

IF OBJECT_ID('tempdb..#EMP') IS NOT NULL
    DROP TABLE #EMP

CREATE TABLE #EMP
(
    EMPID SMALLINT, 
    JOBID SMALLINT, 
    SAL SMALLINT, 
    CITYID SMALLINT,
    YEAR SMALLINT,
    STATUS CHAR(1)
);

INSERT INTO #JOB(JOBID, JOBNAME) 
VALUES (1, 'DEVELOPMENT'),
       (2, 'DEVELOPMENT'),
       (3, 'TESTING'),
       (4, 'TESTING'),
       (7, 'TESTING'),
       (9, 'RESEARCH'),
       (8, 'HR');

INSERT INTO #EMP (EMPID , JOBID, SAL, CITYID, YEAR, STATUS) 
VALUES (100, 1, 1000, 10, 2015, 'A'),
       (200, 2, 2000, 10, 2015, 'A'),
       (300, 1, 2500, 20, 2015, 'A'),
       (400, 3, 1000, 10, 2016, 'A'),
       (500, 6, 3000, 10, 2015, 'E'),
       (600, 8, 1000, 30, 2015, 'A'),
       (700, 8, 2000, 10, 2015, 'E'),
       (800, 9, 1500, 10, 2015, 'A');

;with cteJobDict as (
    select
            distinct
            j.JOBNAME
    from
            #JOB j
)
,cteStatusDict as(
    select
            distinct STATUS
    from
            #EMP e
),cteCityDict as (
    select
            distinct CITYID
    from
            #EMP
)
,cteJobStatusCityMatrix as(
    select
            *
    from
            cteJobDict
    cross apply cteStatusDict
    cross apply cteCityDict
)
,cteEmpWithJobName as (
    select
            e.*
            ,j.JOBNAME
    from
            #EMP e
    join    #JOB j on j.JOBID=e.JOBID
), cteData as (
SELECT
        m.CITYID
        ,CASE WHEN m.STATUS ='A' THEN 'STATUSA' ELSE 'STATUSE' end as [Status-type]
        ,CASE WHEN m.STATUS ='A' THEN 1 ELSE 3 end as [Status-order]
        ,m.JOBNAME
        ,count(distinct e.EMPID) count
        ,iif(count(distinct e.EMPID)>0,'X','') status
        ,isnull(avg(e.sal),0) sal
FROM
        cteJobStatusCityMatrix m
left join cteEmpWithJobName e on e.CITYID=m.CITYID and e.STATUS=m.STATUS and e.JOBNAME=m.JOBNAME and e.YEAR=2015
where
        m.CITYID in (10,20)
group by
        m.CITYID
        ,m.STATUS
        ,m.JOBNAME
union
SELECT
        m.CITYID
        ,'total' as [Status-type]
        ,CASE WHEN m.STATUS ='A' THEN 2 ELSE 4 end as [Status-order]
        ,null
        ,count(distinct e.EMPID) count
        ,iif(count(distinct e.EMPID)>0,'X','') status
        ,isnull(avg(e.sal),0) sal
FROM
        cteJobStatusCityMatrix m
left join cteEmpWithJobName e
            on e.CITYID=m.CITYID
            and e.STATUS=m.STATUS
            and e.JOBNAME=m.JOBNAME
            and e.YEAR=2015 -- here goes year
where
        m.CITYID in (10,20) -- here goes cityid
group by
        m.CITYID
        ,m.STATUS)
select
        CITYID
        ,[Status-type]
        ,JOBNAME
        ,count
        ,status
        ,sal
from
        cteData
order by
        CITYID
        ,[Status-order]