SQL如何创建case语句以基于最大度量单位拉取总和qty

Question

我是SQL新手，正在尝试为仓库创建一个报告，让他们知道每个入库项目的最大单位总数，以便他们可以准备仓库空间。

钢笔和铅笔的最大单位是盒子，纸张是托盘。我如何创建一个case语句来按最大单位求和和过滤？

有两个表：" units“表包含基于基本单位的所有项目及其单位。" orders“表包含已订购的qty的未结订单。

SELECT O.item, O.unit, sum(O.ordered) 

FROM Orders as O
LEFT JOIN Unit as U on U.item = I.item


Item  Unit    Ordered Order No.
----- ----    ------- ---------
Pen    Each   200     50012
Pen    Each   400     50018
Pen    Case   1       50051
Pencil Each   50      50108
Pencil Case   2       50185
Paper  Case   10      50186
Paper  Sheet  240000  50187
Paper  Case   40      50188

Item   Unit  Unit Size
-----  ----  --------
Pen    Each   1
Pen    Box    20
Pen    Case   200
Pencil Each   1
Pencil Box    10
Pencil Case   50
Paper  Sheet  1
Paper  Ream   300
Paper  Case   6000
Paper  Pallet 120000

结果

Item   Unit   Quantity
----   ----   -------- 
Pen    Case   4
Pencil Case   3
Paper  Pallet 2

Answer 1

您的查询需要一个GROUP BY

SELECT O.item, O.unit, SUM(O.ordered) 
FROM Orders O LEFT JOIN
     Unit U 
     ON U.item = I.item
GROUP BY O.item, O.unit;

这将返回已排序的单位。我不能百分之百确定这是你真正想要的，尽管这对我来说是有意义的。

Answer 2

不要认为你正在寻找一个case语句，而是一个有趣的连接。这在postgres中是有效的，row_number()语法可能在你的SQL风格上有所不同。

您需要加入单元表两次。一次查看订单有多少个基本单位。第二次只看每种产品的最大尺寸单位。

运行子查询以查看行级数据，或运行子查询以按项目汇总数据。

select item, preferred_unit, sum(qty_largest_units)
from (
select
    o.item, 
    o.unit, 
    o.order_qty, 
    u.unit_size, 
    o.order_qty*u.unit_size as qty_smallest_units,
    largest_tbl.unit, 
    largest_tbl.unit_size as preferred_unit,
    o.order_qty*u.unit_size/largest_tbl.unit_size as qty_largest_units
from orders o
left join unit u
    on u.item = o.item and o.unit = u.unit
left join (
    select
        item,
        unit,
        unit_size,
        row_number() over (partition by item order by unit_size desc) as rn 
    from unit
) largest_tbl
    on largest_tbl.rn = 1 and largest_tbl.item = o.item
) subquery
group by item, preferred_unit