--我猜测是求count(*),如果是sum某个字段,把count(*)那部分替换了。
select isnull(m.订单MO,n.订单MO) 订单MO ,
isnull(m.flag为1的盒子数,0) flag为1的盒子数,
isnull(n.flag为2的盒子数,0) flag为2的盒子数
from
(
select 订单MO , flag为1的盒子数 = count(*) from Scanbarcode where flag = 1 group by 订单MO
) m,
(
select 订单MO , flag为2的盒子数 = count(*) from Scanbarcode where flag = 2 group by 订单MO
) n
on m.订单MO = n.订单MO
select isnull(m.订单MO,n.订单MO) 订单MO ,
isnull(m.flag为1的盒子数,0) flag为1的盒子数,
isnull(n.flag为2的盒子数,0) flag为2的盒子数
from
(
select 订单MO , flag为1的盒子数 = count(*) from Scanbarcode where flag = 1 group by 订单MO
) m,
(
select 订单MO , flag为2的盒子数 = count(*) from Scanbarcode where flag = 2 group by 订单MO
) n
on m.订单MO = n.订单MO
select 订单MO,
sum(case when flag=1 then 1 else 0 end) as [flag为1的盒子数],
sum(case when flag=2 then 1 else 0 end) as [flag为2的盒子数]
from (
select distinct 订单MO,BarCode,Flag
from Scanbarcode
) as t
group by 订单MO
每个盒子可以装多个产品,也就是同时为flag为1的盒子,可能一个盒号对应4个产品号,按照楼上的统计,统计的是产品数,不是盒数
select isnull(m.MO,n.MO) 订单MO ,
isnull(m.flag为1的盒子数,0) flag为1的盒子数,
isnull(n.flag为2的盒子数,0) flag为2的盒子数
from
(
select MO , flag为1的盒子数 = count(distinct barcode) from scanbarcode where flag = 1 group by MO
) m,
(
select MO , flag为2的盒子数 = count(distinct barcode) from scanbarcode where flag = 2 group by MO
) n
where m.MO = n.MO答案