Помогите с запросом[MS SQL2008]
От: Kaifa Россия  
Дата: 07.02.19 10:39
Оценка:
Есть таблица сотрудников и департамент

CREATE TABLE Employee(id INT PRIMARY KEY CLUSTERED, name VARCHAR(255), salary DECIMAL(19,2), department_id INT)
CREATE TABLE Department(id INT PRIMARY KEY CLUSTERED, name CHAR(255))
ALTER TABLE Employee WITH CHECK ADD CONSTRAINT fk_dep FOREIGN KEY(department_id) REFERENCES dbo.Department(id)
ALTER TABLE Employee WITH CHECK ADD CONSTRAINT fk_emp FOREIGN KEY(chief_id) REFERENCES dbo.Employee(id)


нужно выбрать названия отделов, где самая большая суммарная зп по отделу. я, конечно, написал, но мне кажется можно куда красивше.
напрямую не сказано что нельзя использовать временные таблицы, но я не использую. хотя здесь прямо напрашивается использование. в общем мой вариант:

select 
    dep.NAME 
from (
    select 
        emp.department_id as dep_id, 
        SUM(emp.salary) as sal
    from dbo.Employee emp with(nolock)
    where 
        emp.department_id is not null
    group by emp.department_id
) d
join dbo.department dep with(nolock) on dep.id = d.dep_id
where sal = (
    select 
        max(sal) as sal
    from (
        select 
            emp.department_id as dep_id, 
            SUM(emp.salary) as sal
        from dbo.Employee emp with(nolock)
        where 
            emp.department_id is not null
        group by emp.department_id
    ) d
)
Re: Помогите с запросом[MS SQL2008]
От: Буравчик Россия  
Дата: 07.02.19 10:57
Оценка:
Здравствуйте, Kaifa, Вы писали:

K>Есть таблица сотрудников и департамент

K>
K>CREATE TABLE Employee(id INT PRIMARY KEY CLUSTERED, name VARCHAR(255), salary DECIMAL(19,2), department_id INT)
K>CREATE TABLE Department(id INT PRIMARY KEY CLUSTERED, name CHAR(255))
K>ALTER TABLE Employee WITH CHECK ADD CONSTRAINT fk_dep FOREIGN KEY(department_id) REFERENCES dbo.Department(id)
K>ALTER TABLE Employee WITH CHECK ADD CONSTRAINT fk_emp FOREIGN KEY(chief_id) REFERENCES dbo.Employee(id)
K>


K>нужно выбрать названия отделов, где самая большая суммарная зп по отделу. я, конечно, написал, но мне кажется можно куда красивше.

Как-то так?

SELECT dep.name, salary.sal
FROM (
       SELECT emp.department_id AS dep_id,
              SUM(emp.salary)   AS sal
       FROM dbo.Employee emp
       WHERE emp.department_id IS NOT NULL
       GROUP BY emp.department_id)
       AS salary
       LEFT JOIN dbo.department AS dep ON salary.dep_id = dep.dep_id
ORDER BY salary.sal DESC
Best regards, Буравчик
Re: Помогите с запросом[MS SQL2008]
От: Somescout  
Дата: 07.02.19 10:57
Оценка: 55 (4) +4
Здравствуйте, Kaifa, Вы писали:

K>Есть таблица сотрудников и департамент

K>
K>CREATE TABLE Employee(id INT PRIMARY KEY CLUSTERED, name VARCHAR(255), salary DECIMAL(19,2), department_id INT)
K>CREATE TABLE Department(id INT PRIMARY KEY CLUSTERED, name CHAR(255))
K>ALTER TABLE Employee WITH CHECK ADD CONSTRAINT fk_dep FOREIGN KEY(department_id) REFERENCES dbo.Department(id)
K>ALTER TABLE Employee WITH CHECK ADD CONSTRAINT fk_emp FOREIGN KEY(chief_id) REFERENCES dbo.Employee(id)
K>



K>нужно выбрать названия отделов, где самая большая суммарная зп по отделу. я, конечно, написал, но мне кажется можно куда красивше.
K>напрямую не сказано что нельзя использовать временные таблицы, но я не использую. хотя здесь прямо напрашивается использование.

Может так:

SELECT TOP 1 WITH TIES 
    dep.name, SUM(emp.salary) as total
    FROM Employee emp
    JOIN Department dep on emp.department_id = dep.id
    GROUP BY dep.name
    ORDER BY total DESC
ARI ARI ARI... Arrivederci!
Re[2]: Помогите с запросом[MS SQL2008]
От: Kaifa Россия  
Дата: 07.02.19 11:41
Оценка:
Б>Как-то так?

Б>
Б>SELECT dep.name, salary.sal
Б>FROM (
Б>       SELECT emp.department_id AS dep_id,
Б>              SUM(emp.salary)   AS sal
Б>       FROM dbo.Employee emp
Б>       WHERE emp.department_id IS NOT NULL
Б>       GROUP BY emp.department_id)
Б>       AS salary
Б>       LEFT JOIN dbo.department AS dep ON salary.dep_id = dep.dep_id
Б>ORDER BY salary.sal DESC
Б>



предлагаешь сделать top 1? по-моему это некорректно, т.к. у нескольких отделов теоретически возможна одна и таже сумма.
Re[2]: Помогите с запросом[MS SQL2008]
От: Kaifa Россия  
Дата: 07.02.19 11:47
Оценка:
S>Может так:

S>
S>SELECT TOP 1 WITH TIES 
S>    dep.name, SUM(emp.salary) as total
S>    FROM Employee emp
S>    JOIN Department dep on emp.department_id = dep.id
S>    GROUP BY dep.name
S>    ORDER BY total DESC
S>


та же проблема что и выше по ветке. представь в конторе 10 стандартных отделов полностью укомплектованых машинистками с зп стандартно штатному расписанию
Re[3]: Помогите с запросом[MS SQL2008]
От: Somescout  
Дата: 07.02.19 13:40
Оценка: +2
Здравствуйте, Kaifa, Вы писали:


S>>
S>>SELECT TOP 1 WITH TIES 
S>>    dep.name, SUM(emp.salary) as total
S>>    FROM Employee emp
S>>    JOIN Department dep on emp.department_id = dep.id
S>>    GROUP BY dep.name
S>>    ORDER BY total DESC
S>>


K>та же проблема что и выше по ветке. представь в конторе 10 стандартных отделов полностью укомплектованых машинистками с зп стандартно штатному расписанию


так потому и TOP 1 WITH TIES — если я правильно понимаю, это включает все записи с одинаковым ключём сортировки.
ARI ARI ARI... Arrivederci!
Отредактировано 07.02.2019 13:47 Somescout . Предыдущая версия .
Re: Помогите с запросом[MS SQL2008]
От: Olaf Россия  
Дата: 07.02.19 18:59
Оценка: 11 (3)
Здравствуйте, Kaifa, Вы писали:

K>Есть таблица сотрудников и департамент

K>нужно выбрать названия отделов, где самая большая суммарная зп по отделу. я, конечно, написал, но мне кажется можно куда красивше.
K>напрямую не сказано что нельзя использовать временные таблицы, но я не использую. хотя здесь прямо напрашивается использование. в общем мой вариант:

Ваш запрос можно переписать с использованием CTE. Это не увеличит производительность, но избавит решение от дублирования кода.
;with cte as
(
    select 
        emp.department_id as dep_id, 
        SUM(emp.salary) as sal
    from dbo.Employee emp with(nolock)
    where 
        emp.department_id is not null
    group by emp.department_id
)
select 
    dep.NAME 
from cte d
join dbo.department dep with(nolock) on dep.id = d.dep_id
where sal = (
    select 
        max(sal) as sal
    from cte d
)


И еще пара вариантов к top 1 with ties ...
select d.name
from
(
    select e.department_id as dep_id, 
           sum(e.salary) as sal,
           max(sum(e.salary)) over() as maxsal
    from dbo.Employee e
    where e.department_id is not null
    group by e.department_id
) a
join dbo.Department d on a.dep_id = d.id
where a.sal = a.maxsal

select d.name
from
(
    select e.department_id as dep_id, 
           sum(e.salary) as sal,
           rank() over(order by sum(e.salary) desc) as n
    from dbo.Employee e
    where e.department_id is not null
    group by e.department_id
) a
join dbo.Department d on a.dep_id = d.id
where a.n = 1
Re: Помогите с запросом[MS SQL2008]
От: amg  
Дата: 20.02.19 08:21
Оценка: 2 (1)
Здравствуйте, Kaifa, Вы писали:

K>Есть таблица сотрудников и департамент

с оконными функциями
select 
    id,
    salary_sum
from
(
    select 
        D.id,
        sum(e.salary) salary_sum,
        max(sum(E.salary)) over () salary_max        
    from    
        Employee E
        join Department D on D.id = E.department_id
    group
        by D.id
)q
where
     salary_sum    = salary_max
Отредактировано 20.02.2019 8:24 amg . Предыдущая версия .
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.