gpt4 book ai didi

sql - 查询 : Show the name of the salesmen with more that 4 invoices in the same month

转载 作者:行者123 更新时间:2023-11-29 14:23:49 24 4
gpt4 key购买 nike

PostgreSQL查询:显示当月有4张以上发票的业务员姓名。

表:文章、客户、发票、lines_invoice、省、镇、卖家

我的查询返回值而不考虑同一月的计数,我该怎么做?

select s.codseller, s.name 
from sellers s
join invoices i using (codseller)
group by s.codseller
having count (codinvoice) > 4;

谢谢!

编辑:

屏幕上显示的正确解决方案是: Result

codven = 代码销售商nombre = 名字

对于我的查询,它显示了额外的两行,因为它计算了拥有超过 4 张发票但在不同月份的销售人员。

最佳答案

SELECT s.id, s.name
,date_trunc('month', i.sales_date::timestamp) AS month
,COUNT(i.id) AS invoices_for_month
FROM seller s
INNER JOIN invoices i ON (s.id = i.seller_id)
GROUP BY s.id, s.name, date_trunc('month', i.sales_date::timestamp)
HAVING COUNT(i.id) > 4

测试环境:

CREATE TABLE seller (id int, name text);
INSERT INTO seller VALUES(1, 'Joe');
INSERT INTO seller VALUES(2, 'Mike');
INSERT INTO seller VALUES(3, 'Tom');

CREATE TABLE invoices(id int, seller_id int, sales_date date);
INSERT INTO invoices VALUES(1, 1, now());
INSERT INTO invoices VALUES(2, 1, now() - interval '35' day);
INSERT INTO invoices VALUES(3, 1, now() - interval '37' day);
INSERT INTO invoices VALUES(4, 1, now() - interval '39' day);
INSERT INTO invoices VALUES(5, 1, now() - interval '40' day);
INSERT INTO invoices VALUES(6, 1, now() - interval '40' day);
INSERT INTO invoices VALUES(7, 2, now());

关于sql - 查询 : Show the name of the salesmen with more that 4 invoices in the same month,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10670066/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com