重复采购之和
重复采购之和
提问于 2018-01-20 19:21:03
我正在尝试编写一个查询，从employee_id列表中选择并找到重复的图书购买(book_id)和相关的成本节约(list_price)。如果一个副本存在，它需要复制书_id数量的价格之和。
因此，如果某人有一本与他们的employee_id相关的10美元的书，而这本书又提供给他们，他们就不用再买了，而且节省了10美元。如果再发生这种情况，就会节省20美元。
我尝试了一个having>1，但是我似乎无法正确地获得正确的查询来精确地总结节省的费用。
任何帮助都是非常感谢的。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-01-20 19:49:01
首先，
select employee_id, book_id, count(*)
from book_purchases
group by employee_id, book_id
having count(*) > 1给你需要的名单。
如果我们不必担心价格的变化，那么我们只需添加一两列就可以得到：
select employee_id, book_id,
count(*) as copies_purchased,
sum(list_price) as total_spent,
count(*) - 1 as copies_unnecessarily_purchased,
(count(*) - 1) * avg(list_price) as amount_overspent
from book_purchases
group by employee_id, book_id
having count(*) > 1当然，您可以加入到员工和预订表，以获得名称和标题，以增加一些结果。
要获得每个员工超支的总额，您可以这样包装上面的查询：
select a.employee_id, sum(a.amount_overspent) as total_amount_overspent
from (
select employee_id, book_id,
count(*) as copies_purchased,
sum(list_price) as total_spent,
count(*) - 1 as copies_unnecessarily_purchased,
(count(*) - 1) * avg(list_price) as amount_overspent
from book_purchases
group by employee_id, book_id
having count(*) > 1
) as a
group by a.employee_id最后，我走到一张员工桌旁，我认为这是我在做这件事的时候所拥有的：
select a.employee_id, emp.employee_name, sum(a.amount_overspent) as total_amount_overspent
from (
select employee_id, book_id,
count(*) as copies_purchased,
sum(list_price) as total_spent,
count(*) - 1 as copies_unnecessarily_purchased,
(count(*) - 1) * avg(list_price) as amount_overspent
from book_purchases
group by employee_id, book_id
having count(*) > 1
) as a
inner join employee as emp on emp.employee_id = a.employee_id
group by a.employee_id, emp.employee_name要明确的是，这不是四个单独的查询；它们只是构建最后看到的单个查询的中间阶段。
我希望这能帮到你。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48360177
复制相关文章
相似问题
腾讯云开发者
