有一个球队表,其中就一个NAME列,包含球队A , B , C , D。现要求使用一条SQL语句查询出两两球队进行比赛的所有组合情况,我的SQL是这样写的:select a.name,b.name from test a join test b on a.name!=b.name用自连接查询后总是不知道怎么避免A和B打完后,又出现B和A打的这种重复情况,请高手指点,谢谢!
------解决方案--------------------
- SQL code
SELECT A.NAME, B.NAME FROM TEST A JOIN TEST B ON A.NAME > B.NAME;--测试[TEST@myorcl] SQL>WITH T1 AS 2 (SELECT 'A' COL 3 FROM DUAL 4 UNION ALL 5 SELECT 'B' COL 6 FROM DUAL 7 UNION ALL 8 SELECT 'C' COL 9 FROM DUAL 10 UNION ALL 11 SELECT 'D' COL 12 FROM DUAL 13 )SELECT A.COL,B.COL 14 FROM T1 A,T1 B 15 WHERE A.COL > B.COL 16 ;C C- -D CD BD AC BC AB A