22,209
社区成员
发帖
与我相关
我的任务
分享
-------------明细表
CREATE TABLE tb_detail(
orderDay datetime,
orderID varchar(50),
detailID varchar(50),
yanhuo_number int,
primary key(orderDay,orderID,detailID)
)
------订单表
create table tb_order(
orderDay datetime,
orderID varchar(50),
detailNumber int,
allNumber int,
primary key(orderDay,orderID)
select a.*,
case when b.yhqty=0 then '未验收'
when b.yhqty>0 and b.yhqty<b.itemqty then '验收中'
when b.yhqty=b.itemqty then '已验收'
end '订单状态'
from tb_order a
left join
(select orderID,
count(1) 'itemqty',
sum(case when yanhuo_number is not null then 1 else 0 end) 'yhqty'
from tb_detail
group by orderID) b on a.orderID=b.orderID