sql - 子查询使用来自外部查询的未分组列 "i.date_time"

Question

我有两个表:item_status_log 和 items。 items 表包含 itemid、status 和 ordertype 列。 item_status_log 表具有 itemid、date_time、new_status 和 old_status。基本上，当我的程序中的状态发生变化时，会在 item_status_log 中记录一条记录，其中包含旧状态、新状态和 date_time。

我想要的是能够查看按更新日期分组的项目表。我有以下完美的 sql:

select to_char(date_time, 'MM-DD-YYYY') as "Shipment Date", count(*) as "TOTAL Items"
from item_status_log i where old_status = 'ONORDER' 
group by "Shipment Date" 
order by "Shipment Date" desc

这给了我

Shipment Date  |   TOTAL Items
------------------------------
09/02/2014     |   4
09/01/2014     |   23

但是，我想在上表中添加 2 列，它分解了在“库存”和“订单”的项目表中有多少项目具有状态。

我在找这个:

 Shipment Date  |   TOTAL Items  |  Inventory   |  Ordered 
 ---------------------------------------------------------
 09/02/2014     |   4            |        3     |      1
 09/01/2014     |   23           |       20     |      3

这是我正在尝试的内容，但是从外部查询中获取“子查询使用未分组的列“i.date_time””错误

select to_char(date_time, 'MM-DD-YYYY') as "Shipment Date", count(*) as "TOTAL Items",
(select count(*) from item_status_log t 
where date(t.date_time) = date(i.date_time) and itemid in (select itemid 
from items where  ordertype = 'ORDER')) as "Customer",
(select count(*) from item_status_log t 
where date(t.date_time) = date(i.date_time) and itemid in (select itemid 
from items where  ordertype = 'INVENTORY')) as "Inventory"
from item_status_log i where old_status = 'ONORDER' 
group by "Shipment Date" 
order by "Shipment Date" desc

Answer 1

我认为你只需要条件聚合:

select to_char(date_time, 'MM-DD-YYYY') as "Shipment Date", count(*) as "TOTAL Items",
       sum(case when i.ordertype = 'ORDER' then 1 else 0 end) as NumOrders,
       sum(case when i.ordertype = 'INVENTORY' then 1 else 0 end) as NumInventory
from item_status_log il join
     items i
     on il.itemid = i.itemid
where old_status = 'ONORDER' 
group by "Shipment Date" 
order by "Shipment Date" desc;