例如:按采购订单号PURCHID分组后,统计每个组内InventDimId不一致的PurchId。
原始数据 PurchLine:
PurchId InventDimId
po-001 0001
po-001 0001
po-002 0001
po-002 0001
po-002 0002
po-003 0001
结果:
po-002 2
SELECT
T.PURCHID
,COUNT(*)
from
(
select
PURCHLINE.PURCHID
,INVENTDIMID
from PURCHLINE
join PURCHTABLE
on PURCHLINE.PURCHID = PURCHTABLE.PURCHID
join INVENTTABLE
on PURCHLINE.ITEMID = INVENTTABLE.ITEMID
and INVENTTABLE.ITEMGROUPID = '原材料'
and INVENTTABLE.DATAAREAID = 'Lenovo'
where PURCHLINE.DATAAREAID = 'Lenovo'
and PURCHLINE.PURCHSTATUS = 1
group by
PURCHLINE.PURCHID
,INVENTDIMID
) T
group by T.PURCHID
Having COUNT(*) > 1