sql - 如果我同时拥有(A，B)和(B，A)则仅选择一对

Question

这是一个简单的问题，当我的查询结果为(A，B)时，我只希望拥有(A，B)，而没有(B，A)。

例如，我的查询返回:

161, 52 
161, 53
53, 161
53, 161

这是我的查询:

SELECT S1.SURVEY_ID, S2.SURVEY_ID

  FROM SURVEYS S1, SURVEYS S2

  WHERE (S2.START_DATE BETWEEN S1.START_DATE and S1.END_DATE
  OR S2.END_DATE BETWEEN S1.START_DATE and S1.END_DATE)
  AND S1.SURVEY_ID != S2.SURVEY_ID

ORDER BY S1.SURVEY_ID, S2.SURVEY_ID

Answer 1

这是一种方法:

WITH SS as (
      SELECT S1.SURVEY_ID as SURVEY_ID1, S2.SURVEY_ID as SURVEY_ID2
      FROM SURVEYS S1 JOIN
           SURVEYS S2
           ON (S2.START_DATE BETWEEN S1.START_DATE and S1.END_DATE OR
               S2.END_DATE BETWEEN S1.START_DATE and S1.END_DATE
              ) AND
              S1.SURVEY_ID <> S2.SURVEY_ID
     )
SELECT ss.*
FROM ss
WHERE SURVEY_ID1 < SURVEY_ID2
UNION ALL
SELECT ss.*
FROM ss
WHERE SURVEY_ID1 > SURVEY_ID2 AND
      NOT EXISTS (SELECT 1 FROM ss ss2 WHERE ss2.SURVEY_ID1 = ss.SURVEY_ID2 AND ss2.SURVEY_ID2 = ss.SURVEY_ID1);

但是，如果您只想重叠调查，那么这将是适当的查询:

      SELECT S1.SURVEY_ID as SURVEY_ID1, S2.SURVEY_ID as SURVEY_ID2
      FROM SURVEYS S1 JOIN
           SURVEYS S2
           ON S2.START_DATE <= S1.END_DATE AND
              S2.END_DATE >= S1.START_DATE 
              S1.SURVEY_ID < S2.SURVEY_ID;