Hello!

There is a table consisting of 3 columns: id (int) , A (int) and B(int) . It is filled with pairs of random values A and B in the range from 0 to N inclusive, with A i ≠ B i . Then x is selected from the same range from 0 to N inclusive.

Need a query that retrieves records for which the following conditions are true:

  1. Or A , or B is x .
  2. Pair A, B unique among all pairs (For example, if there is (3, 1) , then there can be no other pair (3, 1) or (1, 3) ).
  3. If there are several suitable pairs in the old table, then the eldest one by id is selected.

Examples

Source table with N = 5 :

 id | A | B ---+---+--- 1 | 5 | 4 2 | 0 | 4 3 | 4 | 3 4 | 3 | 5 5 | 4 | 2 6 | 1 | 2 7 | 0 | 4 8 | 0 | 5 9 | 0 | 5 10 | 5 | 0

Sample:

  1. when x = 0 :

     id | A | B ---+---+--- 7 | 0 | 4 - был еще вариант 2 | 0 | 4, но 7 > 2 10 | 5 | 0 - был еще вариант 8 | 0 | 5 и 9 | 0 | 5, но 10 > 9 и 10 > 8

  2. with x = 1 :

     id | A | B ---+---+--- 6 | 1 | 2

  3. with x = 2 :

     id | A | B ---+---+--- 5 | 4 | 2

    6 | 1 | 2

  4. with x = 3 :

     id | A | B ---+---+--- 3 | 4 | 3 4 | 3 | 5

  5. with x = 4 :

     id | A | B ---+---+--- 1 | 5 | 4 3 | 4 | 3 5 | 4 | 2 7 | 0 | 4 - был еще вариант 2 | 0 | 4, но 7 > 2

  6. with x = 5 :

     id | A | B ---+---+--- 1 | 5 | 4 4 | 3 | 5 10 | 5 | 0 - был еще вариант 8 | 0 | 5 и 9 | 0 | 5, но 10 > 9 и 10 > 8

Naturally, this can be solved within the framework of the program code, so the question is precisely in the request to MySql.

    2 answers 2

    In t-sql

     declare @x int = 0 select * from table select * from table where id in ( select MAX(id) from table where a = @x or b = @x group by a + b)

      something like this:

       SET @prm = 0; SELECT a.* FROM test a JOIN ( SELECT DISTINCT CASE WHEN b.leftp IS NOT NULL AND a.id < b.id THEN b.id ELSE a.id END as id FROM (SELECT leftp, rightp, max(id) as id from test WHERE leftp = @prm OR rightp = @prm GROUP BY leftp, rightp) a LEFT JOIN (SELECT leftp, rightp, max(id) as id from test WHERE leftp = @prm OR rightp = @prm GROUP BY leftp, rightp) b ON a.leftp = b.rightp AND a.rightp = b.leftp ) b ON a.id = b.id