一个检查在同一个供货商同一个ITEM中有多个Business Partner Catalog Numbers的Query

今天完成了一个检查在同一个供货商同一个ITEM中有多个Business Partner Catalog Numbers的Query， 如下：

 

SELECT     ItemCode, CardCode, Substitute AS BPCatalogNo

FROM         dbo.OSCN

WHERE     (ItemCode IN

                          (SELECT     ItemCode

                            FROM          dbo.OSCN AS OSCN_3

                            GROUP BY ItemCode, CardCode

                            HAVING      (COUNT(Substitute) > 1))) AND (CardCode NOT IN

                          (SELECT     CardCode

                            FROM          (SELECT     CardCode

                                                    FROM          dbo.OSCN AS OSCN_2

                                                    WHERE      (ItemCode IN

                                                                               (SELECT     ItemCode

                                                                                 FROM          dbo.OSCN AS OSCN_1

                                                                                 GROUP BY ItemCode, CardCode

                                                                                 HAVING      (COUNT(Substitute) > 1)))) AS t1

                            GROUP BY CardCode

                            HAVING      (COUNT(CardCode) = 1)))