Mysql Почему, когда я использую группу по идентификатору, функция суммы не вычисляет суммирование столбца
У нас есть сотрудники в нашей компании, сотрудники занимают деньги из своей зарплаты, и эти заемные деньги должны быть известны нам. также нам нужно знать остаток денег от зарплаты в каждой сделке, и сумму заемных денег.
Я создал таблицу сотрудников, которая включает столбец "Зарплата"
как
CREATE TABLE `employees` (
`Employee_Id` int(11) NOT NULL AUTO_INCREMENT,
`Employee_Name` varchar(100) NOT NULL,
`Salary` decimal(10,0) NOT NULL,
PRIMARY KEY (`Employee_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2
И таблица pay_transaction для сохранения каждой заемной суммы в столбце money_amount.
CREATE TABLE salary_transaction (
Salary_Transaction_Id int(11) NOT NULL AUTO_INCREMENT,
Employee_Id int(11) NOT NULL,
money_amount decimal(10,0) NOT NULL,
PRIMARY KEY (Salary_Transaction_Id)
) ENGINE=InnoDB AUTO_INCREMENT=3
Это мой запрос, и проблема в том, что он не вычисляет совокупную сумму money_amount, заимствованную конкретным сотрудником.
SELECT t.Salary_Transaction_Id,
t.Employee_Id,t.money_amount,
sum(t.money_amount) as Total_borrowed,
e.salary-sum(t.money_amount) as remaining from salary_transaction t
JOIN
(SELECT salary,Employee_Id,Employee_Name from employees ) e
ON
t.Employee_Id = e.Employee_Id GROUP by t.salary_transaction_id
Редактировать
Я предоставил свои вопросы со сценариями
Редактировать 2
Ожидаемые значения total_borrowed
Ответы 2
Вы хотите вычесть общую сумму, заимствованную каждым сотрудником, из его зарплаты. Я бы рекомендовал left join с предварительной агрегацией:
select e.*,
coalesce(st.total_borrowed, 0) as total_borrowed,
e.salary - coalesce(st.total_borrowed, 0) as remaining
from employee e
left join (
select employee_id, sum(money_amount) as total_borrowed
from salary_transaction
group by employee_id
) st on st.employee_id = e.employee_id
Вы также можете использовать коррелированный подзапрос. В самых последних версиях MySQL боковые соединения пригодятся:
select e.*, st.total_borrowed, e.salary - st.total_borrowed as remaining
from employee e
cross join lateral (
select coalesce(sum(money_amount), 0) as total_borrowed
from salary_transaction st
where st.employee_id = e.employee_id
) st
Обновлено: оба запроса дают вам одну строку для каждого сотрудника. Если вам действительно нужна одна строка для каждой транзакции с текущей суммой заемных денег и оставшейся зарплатой, тогда все по-другому.
В MySQL 8.0 вы можете использовать оконные функции:
select e.*,
st.salary_transaction_id, st.money_amount,
coalesce(sum(st.money_amount) over(partition by employee_id order by st.salary_transaction_id), 0) as total_borrowed,
e.salary - coalesce(sum(st.money_amount) over(partition by employee_id order by st.salary_transaction_id), 0) as remaining
from employee e
left join salary_transaction st using(employee_id)
В более ранних версиях альтернативой является коррелированный подзапрос:
select t.*, salary - total_borrowed
from (
select e.*,
st.salary_transaction_id, st.money_amount,
(
select coalesce(sum(st.money_amount), 0)
from salary_transaction st1
where st.employee_id = e.employee_id and st1.salary_transaction_id <= st.salary_transaction_id
) as total_borrowed
from employee e
left join salary_transaction using(employee_id)
) t
ваш первый запрос не показывает мне все транзакции
и второй запрос имеет ошибку в синтаксисе SQL.
@Nefazodone: так вы хотите одну строку на транзакцию, а не одну строку на сотрудника? Смотрите мое обновление. Обратите внимание, что решение зависит от вашей версии MySQL (это уже было ясно упомянуто для второго запроса), поэтому обязательно используйте решение, подходящее для вашей версии базы данных.
Мне нужна строка для каждой транзакции. Моя maiadb не очень свежая.
пожалуйста, посмотрите на мой Edit 2
select t.salary_transaction_id,
t.employee_id,
t.money_amount,
sum(t.money_amount) over (partition by employee_id order by t.salary_transaction_id) as total_borrowed,
e.salary - sum(t.money_amount) over (partition by e.employee_id order by t.salary_transaction_id) remaining
from employees e
inner join salary_transaction t on t.employee_id = e.employee_id
group by t.employee_id, t.salary_transaction_id, t.money_amount
Вы можете использовать функцию НАД ().
salary_transaction_id | employee_id | money_amount | total_borrowed | remaining
1 | 1 | 3000 | 3000 | 4000
2 | 1 | 1000 | 4000 | 3000
3 | 1 | 500 | 4500 | 2500
Добро пожаловать в СО. См. Почему я должен предоставлять MCRE для того, что мне кажется очень простым SQL-запросом