Блог

У меня есть две таблицы 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,
    SEX  CHAR(1),
    STATE CHAR(2)
);

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

INSERT INTO EMP (EMPID , JOBID, SAL, CITYID, YEAR, SEX,STATE) 
VALUES (100, 1, 1000, 10, 2015, 'M','NY'),
       (200, 2, 2000, 10, 2015, 'M','NJ'),
       (300, 1, 2500, 20, 2015, 'F','NY'),
       (400, 3, 1000, 10, 2015, 'M','CA'),
       (500, 6, 3000, 10, 2015, 'F','NJ'),
       (600, 8, 1000, 30, 2015, 'M','CA'),
       (700, 8, 2000, 10, 2015, 'F','NY'),
       (800, 9, 1500, 10, 2015, 'F','CA');
  

Для заданного входного идентификатора города, ГОДА (таблица Emp) возьмите все идентификаторы вакансий для каждого имени вакансии (из таблицы job ) и сопоставьте в таблице Emp, если существует отображение count (количество идентификаторов вакансий, присутствующих в таблице Emp) каждого пола (мужского и женского) для штатов NY, NJ, CA в горизонтальном виде; а также итоговые значения. если совпадения нет, отобразите 0.

Вывод должен быть таким:

 Jobname                  Male           FEMALE              TOTAL
                    NY   NJ   CA      NY   NJ   CA 

Development         1     1   0       1    0    0             3
TESTING             0     0   1       0    0    0             1
HR                  0     0   1       1    0    0             2
RESEARCH            0     0   0       0    0    0             0
TOTAL               1     1   2       2    0    0             6     
  

Как получить результаты в горизонтальном виде ..?

Привет, я думаю, это можно использовать :

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

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

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

INSERT INTO #EMP (EMPID , JOBID, SAL, CITYID, YEAR, SEX,STATE) 
VALUES (100, 1, 1000, 10, 2015, 'M','NY'),
       (200, 2, 2000, 10, 2015, 'M','NJ'),
       (300, 1, 2500, 20, 2015, 'F','NY'),
       (400, 3, 1000, 10, 2015, 'M','CA'),
       (500, 6, 3000, 10, 2015, 'F','NJ'),
       (600, 8, 1000, 30, 2015, 'M','CA'),
       (700, 8, 2000, 10, 2015, 'F','NY'),
       (800, 9, 1500, 10, 2015, 'F','CA');



    SELECT * FROM #JOB;

    SELECT * FROM #EMP;


    SELECT J.JOBNAME, 
        SUM(CASE WHEN E.SEX = 'M' AND STATE = 'NY' THEN 1 ELSE 0 END) AS 'Male_NY',
        SUM(CASE WHEN E.SEX = 'M' AND STATE = 'NJ' THEN 1 ELSE 0 END) AS 'Male_NJ',
        SUM(CASE WHEN E.SEX = 'M' AND STATE = 'CA' THEN 1 ELSE 0 END) AS 'Male_CA',
        SUM(CASE WHEN E.SEX = 'F' AND STATE = 'NY' THEN 1 ELSE 0 END) AS 'Female_NY',
        SUM(CASE WHEN E.SEX = 'F' AND STATE = 'NJ' THEN 1 ELSE 0 END) AS 'Female_NJ',
        SUM(CASE WHEN E.SEX = 'F' AND STATE = 'CA' THEN 1 ELSE 0 END) AS 'Female_CA',
        COUNT(*) AS 'Total'
    FROM #EMP E
    JOIN #JOB J ON E.JOBID = J.JOBID
    GROUP BY J.JOBNAME


    DROP TABLE #JOB;

    DROP TABLE #EMP;
  

Но это статический режим, если у вас есть какое-то другое состояние и пол, вы должны создать это с помощью динамического SQL-запроса и использовать проверку ВЫПОЛНЕНИЯ по этой ссылке.

• https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017

Другим способом вы можете использовать сводную проверку по этой ссылке :

• https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

Во-первых, SQL-запросы не поддерживают заголовки нескольких уровней. У вас могут быть сложные строки заголовка, но не несколько уровней.

Во-вторых, вам нужны строки без сотрудников.

В-третьих, вам нужна общая строка.

Итак, вам понадобится внешнее соединение:

 SELECT COALESCE(J.JOBNAME, 'TOTAL') as JobName,
       SUM(CASE WHEN E.SEX = 'M' AND E.STATE = 'NY' THEN 1 ELSE 0 END) AS Male_NY,
       SUM(CASE WHEN E.SEX = 'M' AND E.STATE = 'NJ' THEN 1 ELSE 0 END) AS Male_NJ,
       SUM(CASE WHEN E.SEX = 'M' AND E.STATE = 'CA' THEN 1 ELSE 0 END) AS Male_CA,
       SUM(CASE WHEN E.SEX = 'F' AND E.STATE = 'NY' THEN 1 ELSE 0 END) AS Female_NY,
       SUM(CASE WHEN E.SEX = 'F' AND E.STATE = 'NJ' THEN 1 ELSE 0 END) AS Female_NJ,
       SUM(CASE WHEN E.SEX = 'F' AND E.STATE = 'CA' THEN 1 ELSE 0 END) AS Female_CA
FROM JOB J LEFT JOIN
     EMP E
     ON E.JOBID = J.JOBID AND
        E.YEAR = @YEAR AND
        E.CITYID = @CITYID
GROUP BY GROUPING SETS ( (J.JOBNAME), () );
  

Если вам нужны данные по всем годам / городам, удалите условия в ON предложении.

Здесь находится db<>fiddle.