mysql - 如何获得带有group by子句的左联接以返回所有行？

我正在尝试编写查询以确定在给定时间（即当前时间，下个月等）承诺的库存量。

一个简化的例子：

我有一个物品清单表。我有一个报价表，用于指定客户，报价何时开始以及报价何时过期。我有将两者关联的第三张桌子。

create table inventory
(id int not null auto_increment , name varchar(32) not null, primary key(id));

create table offer
(id int not null auto_increment , customer_name varchar(32) not null, starts_at datetime not null,  expires_at datetime, primary key (id));

create table items
(id int not null auto_increment, inventory_id int not null, offer_id int not null, primary key (id),
  CONSTRAINT fk_item__offer FOREIGN KEY (offer_id)  REFERENCES offer(id),
  CONSTRAINT fk_item__inventory FOREIGN KEY (inventory_id)  REFERENCES inventory(id));

创建一些库存

insert into inventory(name)
    values ('item 1'), ('item 2'),('item 3');

为这个月创建两个报价

insert into offer(customer_name, starts_at)
    values ('customer 1', DATE_FORMAT(NOW(), '%Y-%m-01')), ('customer 2', DATE_FORMAT(NOW(), '%Y-%m-01'));

下个月一个

insert into offer(customer_name, starts_at)
    values ('customer 3', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01'));

现在向每个报价添加一些项目

insert into items(inventory_id, offer_id)
    values (1,1), (2,1), (2,2), (3,3);

我想要的是一个查询，该查询将向我显示该月的所有库存和已承诺库存的数量。如果starts_at小于或等于现在，并且报价尚未过期（expires_at为null或expires_at在将来），则认为库存已提交。

我期望的结果如下所示：

+----+--------+---------------------+
| id | name   | committed_inventory |
+----+--------+---------------------+
|  1 | item 1 |                   1 |
|  2 | item 2 |                   2 |
|  3 | item 3 |                   0 |
+----+--------+---------------------+
3 rows in set (0.00 sec)

我认为应该工作的查询是：

SELECT inventory.id
     , inventory.name
     , count(items.id) as committed_inventory
  FROM inventory
  LEFT JOIN items
    ON items.inventory_id = inventory.id
  LEFT JOIN offer
    ON offer.id = items.offer_id
 WHERE (offer.starts_at IS NULL OR offer.starts_at <= NOW())
   AND (offer.expires_at IS NULL OR offer.expires_at > NOW())
 GROUP BY inventory.id, inventory.name;

但是，此查询的结果不包括第三项。我得到的是：

+----+--------+---------------------+
| id | name   | committed_inventory |
+----+--------+---------------------+
|  1 | item 1 |                   1 |
|  2 | item 2 |                   2 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

我无法弄清楚如何显示第三个库存项目。由于库存是外部联接的推动因素，因此我认为应该始终显示库存。

最佳答案

问题是where子句。尝试这个：

SELECT inventory.id
     , inventory.name
     , count(offers.id) as committed_inventory
  FROM inventory
  LEFT JOIN items
    ON items.inventory_id = inventory.id
  LEFT JOIN offer
    ON offer.id = items.offer_id and
       (offer.starts_at <= NOW() or
        offer.expires_at > NOW()
      )
 GROUP BY inventory.id, inventory.name;

问题是您获得了匹配的报价，但当前无效。因此，where子句失败，因为要约日期不是NULL（存在匹配项），并且日期比较失败，因为要约日期不是当前日期。