declare @temp varchar(50)
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT t.PACK_CODE FROM T_MDM_PRO_PRODUCT_STRUCT t group by t.PACK_CODE) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor into @temp; --读取第一行数据
WHILE @@FETCH_STATUS = 0
BEGIN
print @temp;
select PID,PACK_CODE,MA_CODE from T_MDM_PRO_PRODUCT_STRUCT where PACK_CODE in
(
select PACK_CODE from T_MDM_PRO_PRODUCT_STRUCT where MA_CODE in
(select MA_CODE from T_MDM_PRO_PRODUCT_STRUCT where PACK_CODE=@temp)
group by PACK_CODE
)
FETCH NEXT FROM My_Cursor; --读取下一行数据
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
GO
select E.PACKCODES,E.countnum,A.MA_CODE from(
select PACKCODES,COUNT(MA_CODE) as countnum from(
SELECT tb.MA_CODE,STUFF(
(SELECT '|'+CONVERT(VARCHAR,t.PACK_CODE) FROM T_MDM_PRO_PRODUCT_STRUCT t where t.MA_CODE=tb.MA_CODE order by t.PACK_CODE FOR XML PATH('')
),1,1,'') as PACKCODES
FROM T_MDM_PRO_PRODUCT_STRUCT tb
group by MA_CODE)B
group by PACKCODES
having COUNT(MA_CODE)>1
)E
left join
(
SELECT tb.MA_CODE,STUFF(
(SELECT '|'+CONVERT(VARCHAR,t.PACK_CODE) FROM T_MDM_PRO_PRODUCT_STRUCT t where t.MA_CODE=tb.MA_CODE order by t.PACK_CODE FOR XML PATH('')
),1,1,'') as PACKCODES
FROM T_MDM_PRO_PRODUCT_STRUCT tb
group by MA_CODE
)A
ON E.PACKCODES=A.PACKCODES
order by E.PACKCODES