查找组内某字段不一致的组



例如：按采购订单号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