Как найти сложный SQL-запрос
У меня есть такая таблица:
Таблица: Работник
EmpId: INTEGER NOT NULL
DeptId: INTEGER NOT NULL
Name: Char(40) NOT NULL
Age: INTEGER NOT NULL
Salary: INTEGER NOT NULL
Таблица: отделение
DeptId: INTEGER NOT NULL
Name: Char(40) NOT NULL
Rank: INTEGER NULL
Мне нужно найти следующий запрос: -
a) Ранжировать весь отдел в порядке убывания среднего возраста всех сотрудников в этом отделе, а затем соответствующим образом обновить столбец ранжирования. Таким образом, отдел с наивысшим средним возрастом сотрудников должен получить 1 место, отдел со вторым по величине средним возрастом сотрудников - 2 и так далее.
В случае, если средний возраст сотрудников нескольких отделов одинаков, их следует ранжировать по уникальному идентификатору DeptId. Рейтинг отдела без какого-либо сотрудника обновлять не следует.
б) Перечислить названия отделов вместе с общими бюджетами отдела. Общий бюджет просто определяется как сумма заработной платы сотрудников в этом отделе. Если в отделе нет сотрудников, их сумма должна быть равна 0.
c) SQL-запрос для вывода названий всех отделов таким образом, чтобы средняя зарплата в отделе превышала 10,00,000.
Смотрите meta.stackoverflow.com/questions/333952/… и успокаивайте этих циников
Вы изучили команды SQL AVG (для получения среднего возраста и заработной платы), RANK, DENSE_RANK или ROW_NUMBER для функции ранжирования? Мне это очень похоже на домашнее задание ...
Это не сложнее, чем приготовление спагетти. Пожалуйста, не называйте простые соединения сложными - подождите, пока ваши sql-операторы охватят 2-3 страницы, прежде чем называть их сложными.
Ответы 1
Я создал временные таблицы и данные для выполнения запросов
CREATE TABLE #Employee(
EmpId INTEGER NOT NULL,
DeptId INTEGER NOT NULL,
Name Char(40) NOT NULL,
Age INTEGER NOT NULL,
Salary INTEGER NOT NULL
);
CREATE TABLE #Department(
DeptId INTEGER NOT NULL,
Name Char(40) NOT NULL,
Rank INTEGER NULL
);
INSERT INTO #Employee VALUES(1,1,'A',34,9000000),
(2,2,'B',25,450000),
(3,1,'C',45,600000),
(4,1,'D',23,340000),
(5,2,'E',21,580000),
(6,3,'F',26,7500000),
(7,1,'G',27,650000),
(8,2,'H',28,410000),
(9,1,'I',29,120000),
(10,2,'J',54,150000),
(11,3,'I',65,1500000);
INSERT INTO #Department (DeptId,Name) VALUES(1,'Dept 1'),
(2,'Dept 2'),
(3,'Dept 3');
И ниже приведены запросы, которые будут соответствовать вашим требованиям.
а)
UPDATE #Department SET RANK=a.Rank
FROM (SELECT DeptId,AVG(Age) AS 'AvgAge',ROW_NUMBER() OVER (ORDER BY AVG(AGE)DESC,DeptId ASC) AS 'Rank'
FROM #Employee GROUP BY DeptId)a
WHERE #Department.DeptId=a.DeptId
б)
SELECT d.Name,SUM(e.Salary) AS 'Total Budget'
FROM #Employee e JOIN #Department d
ON e.DeptId=d.DeptId
GROUP BY d.Name
в)
SELECT d.Name,AVG(e.Salary) AS 'Avg Sal > 1000000'
FROM #Employee e JOIN #Department d
ON e.DeptId=d.DeptId
GROUP BY d.Name HAVING AVG(e.Salary)>1000000
Какая это должна быть версия SQL?
Это SQL Server.
SQL-сервер или MySQL? Вы можете улучшить этот вопрос, включив образцы данных и ожидаемый результат. И вы должны показать, что вы пробовали, иначе циники могут заподозрить, что вы хотите, чтобы они делали вашу работу за вас.