如何拆分订单数量为已拣货、剩余行并添加状态标识列

表结构与测试数据 #

create table orders (
  id int not null,
  item varchar(10),
  quantity int
);

insert into orders (id, item, quantity) values
(1, 'Item 1', 10);

create table orders_picked (
  id int not null,
  orderId int,
  quantity int
);

insert into orders_picked (id, orderId, quantity) values
(1, 1, 4),
(2, 1, 1);

现有查询与需求 #

我原本使用以下SQL统计已拣货商品数量：

select item, sum(op.quantity) as quantity 
from orders o left join orders_picked op on o.id = op.orderId 
group by item

该查询返回Item 1已拣货5件，但我需要将已拣货数量和剩余待拣货数量分两行展示，同时新增一列标注状态为「已拣货」或「剩余」，最终输出需包含两行数据：

• 第一行：Item 1，数量5，状态「已拣货」
• 第二行：Item 1，数量5，状态「剩余」

解决方案 #

方法1：使用UNION ALL合并两个统计查询 #

分别统计已拣货量和剩余量，再合并结果集：

-- 统计已拣货数据
select 
    o.item,
    sum(op.quantity) as quantity,
    '已拣货' as status
from orders o
left join orders_picked op on o.id = op.orderId
group by o.item

union all

-- 统计剩余待拣货数据
select 
    o.item,
    o.quantity - coalesce((select sum(quantity) from orders_picked where orderId = o.id), 0) as quantity,
    '剩余' as status
from orders o;

方法2：使用CTE预计算总量与拣货量，再生成状态行 #

先通过CTE一次性计算商品总数量和已拣货数量，再通过CROSS JOIN生成两种状态的行，性能更优：

with order_stats as (
    select 
        o.id,
        o.item,
        o.quantity as total_qty,
        coalesce(sum(op.quantity), 0) as picked_qty
    from orders o
    left join orders_picked op on o.id = op.orderId
    group by o.id, o.item, o.quantity
)
select 
    item,
    case status 
        when '已拣货' then picked_qty 
        else total_qty - picked_qty 
    end as quantity,
    status
from order_stats
cross join (
    select '已拣货' as status 
    union 
    select '剩余' as status
) as status_list;

两种方法都能满足需求，方法2仅需一次表关联，在数据量较大时更高效。

内容的提问来源于stack exchange，提问作者user19768148