如何在SQL中编写带分组的Cross Join并排除自匹配结果

你的问题核心是要排除交叉连接中的自匹配记录，同时保留同组（Name和Amount一致）的两两配对。这里有两种可行的写法：

方法一：在WHERE条件中排除自匹配 #

直接在你原有的查询基础上，添加一个A.ID <> B.ID的条件，就能过滤掉PrimaryID和SecondaryID相同的记录：

SELECT A.ID AS PrimaryID, B.ID AS SecondaryID, 
       A.Name + CAST(A.Amount AS VARCHAR(10)) AS CommonID
FROM TEMPOC AS A
CROSS JOIN TEMPOC AS B 
WHERE A.Amount = B.Amount 
  AND A.Name = B.Name
  AND A.ID <> B.ID

方法二：改用INNER JOIN（更规范） #

CROSS JOIN本身是笛卡尔积，不支持直接加ON条件，改用INNER JOIN可以把关联逻辑和排除自匹配的条件都放在ON子句里，逻辑更清晰，执行效率也可能更好：

SELECT A.ID AS PrimaryID, B.ID AS SecondaryID, 
       A.Name + CAST(A.Amount AS VARCHAR(10)) AS CommonID
FROM TEMPOC AS A
INNER JOIN TEMPOC AS B 
  ON A.Name = B.Name 
  AND A.Amount = B.Amount
  AND A.ID <> B.ID

两种写法都能得到你想要的结果集：既保留同组记录的两两双向配对，又去掉了ID相同的自匹配行。

内容的提问来源于stack exchange，提问作者Tech Learner