SQL Server：匹配两个表并按照适当的列顺序返回结果

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');

我想显示所有工作名称的数量和平均工资；如果工作ID不存在，则显示0。

对于给定的输入城市ID、年份和状态（来自Emp表），取出每个工作名称的所有工作ID（从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年。首先显示A状态的结果，然后是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 );

错误： 在选择列表中，列 'EMP1.STATUS' 无效，因为它既不包含在聚合函数中，也没有出现在GROUP BY子句中。

- SRI

---

请分享您已经编写的查询和您所面临的挑战，这样就更容易帮助您了。 - Mukesh Arora

在问题中添加了我的查询 - SRI

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]

- buniek