Mysql将两个表与联接或子查询组合在一起

Question

我有两张桌子。第一表有产品，第二表有代理商的销售。每个代理商可以销售一个或更多的产品，但不超过"max_sales_allowed_per_agent“。我只对目前的经纪人感兴趣，我们叫他“约翰”(agent_id)吧。我想要一个名单，所有的产品，约翰还没有出售，最大允许的nr，该项目尚未。我试着进行子选择和连接，但是我无法使它工作：-

我需要什么？

我需要所有的产品：

    agent_id = John (Table SALES)
    status of product is "active" (Table PRODUCTS)
    number of sales John made is less than "max_sales_required_per_agent" 

表产品

    product_id
    status [active|inactive]
    max_sales_required_per_agent

表销售每个人都可以做x相同产品的销售

    agent_id
    date_of_sale
    product_id_sold

表中数据的示例

产品：

    Book   active   2
    Pen    active   3
    Laptop inactive 2
    Camera active   11

销售：

    John  2022-10-01  Book
    John  2022-10-02  Book
    Nancy 2022-09-03  Pen
    Jack  2022-08-11  Book
    John  2022-07-22  Camera
    John  2022-05-01  Camera
    John  2022-03-03  Laptop

结果期望

我需要查询以返回所有尚未到达max_sales_required的活动产品，我需要销售约翰为产品和max_sales_required生产的nr。我需要创建一个查询来为John返回以下内容：

    Laptop (1 sale out of 2 possible) --> not returning because product is inactive
    Camera (2 sales out of 11 possible)
    Book (2 sales out of 2 possible) --> not returning because 2 sales of 2 max allowed

请帮帮我！

Answer 1

也许是这样：

select p1.product_id,
       p1.max_sales_required_per_agent,
       p1.status,
       tbl.prod_sold
from products p1
inner join ( select s.product_id_sold,count(*) as prod_sold
             from sales s
             inner join products p on p.product_id=s.product_id_sold
             where agent_id='John' and p.status='active'
             group by product_id_sold 
           ) tbl on tbl.product_id_sold=p1.product_id and prod_sold < max_sales_required_per_agent;

结果：

product_id max_sales_required_per_agent status prod_sold相机11活动2

https://dbfiddle.uk/9HuBWS-h

如果状态具有活动/非活动值，并且只想选择所有值都处于活动状态的产品，则应添加having count(distinct status) = 1条件，如下所示：

select p1.product_id,
       p1.max_sales_required_per_agent,
       p1.status,
       tbl.prod_sold
from products p1
inner join ( select s.product_id_sold,count(*) as prod_sold
             from sales s
             inner join products p on p.product_id=s.product_id_sold
             where agent_id='John' and p.status='active'
             group by product_id_sold 
             having count(distinct status) = 1
           ) tbl on tbl.product_id_sold=p1.product_id and prod_sold < max_sales_required_per_agent;