mysql - How to select from two tables in a specific order to associate idT1-idT2

Question

I always had this question:

How to select this in SQL (MySQL):

T1
A
3
4
5
6
7

T2
B
45
63
75
82
95

Result
3-45
4-63
5-75
6-82
7-95

As you can see, the idea is to associate both tables in the same order. I tryed this:

SELECT T1.A, T2.B FROM 
    (SELECT * FROM T1 ORDER BY A DESC) T1,
    (SELECT * FROM T2 ORDER BY B DESC) T2
GROUP BY T1.A DESC

I get:

3-45
4-45
5-45
6-45
7-45

No success.

标签： mysqlselect

Answer 1

DROP TABLE IF EXISTS T1;

CREATE TABLE t1
(A INT NOT NULL PRIMARY KEY);

INSERT INTO t1 VALUES
(3),
(4),
(5),
(6),
(7);

DROP TABLE IF EXISTS T2;

CREATE TABLE t2
(B INT NOT NULL PRIMARY KEY);

INSERT INTO t2 VALUES
(45),(63),(75),(82),(95);

SELECT x.a
     , y.b 
  FROM 
     ( SELECT a, @i:=@i+1 i FROM t1, (SELECT @i:=0) vars ORDER BY a) x 
  JOIN 
     ( SELECT b, @j:=@j+1 j FROM t2, (SELECT @j:=0) vars ORDER BY b) y 
    ON y.j = x.i;
+---+----+
| a | b  |
+---+----+
| 3 | 45 |
| 4 | 63 |
| 5 | 75 |
| 6 | 82 |
| 7 | 95 |
+---+----+