mysql - 如何在MySQL中连接两个表而不重复两列

Question

我有两个表(T1/t2):

      T1                         T2
   id | value                 id | value
    1 |  10                    1 |   5
    2 |  10                    2 |  15
    3 |   5                    3 |  15
    4 |  15                    4 |   5
    5 |   5                    5 |  10

我根据 T1 选择 T2 记录(如果 T1 具有 >= 具有相同值的记录数量):

SELECT t.id t2_id, t.value value
FROM t2 as t
WHERE 
(SELECT COUNT(*) FROM t1 WHERE t1.value = t.value) >= (SELECT COUNT(*) FROM t2 WHERE t2.value = t.value)

正如预期的那样，此返回:

   t2_id  |  value
      1   |   5
      4   |   5
      5   |   10

问题是当我尝试附加 t1_id 列时没有重复任何 id。顺序并不重要。所以基本上我正在寻找这个结果:

  t1_id  | t2_id  |  value
     3   |   1    |   5
     5   |   4    |   5
     1   |   5    |   10

每次 JOIN 和 Group 尝试都以第一个的 t1_id 结束与每个值 block 匹配的记录。

Answer 1

MariaDB [sandbox]> SELECT T1.ID,T2.ID,T1.VALUE
    -> FROM
    -> (
    -> SELECT * FROM
    -> (
    -> SELECT T2.*,(SELECT COUNT(*) FROM T2 T21 WHERE T21.VALUE = T2.VALUE) T2OBS,
    -> (SELECT COUNT(*) FROM T1 WHERE T1.VALUE = T2.VALUE) T1OBS,
    -> IF(T2.VALUE <> @P,@RN:=1,@RN:=@RN+1) RN,
    -> @P:=T2.VALUE P
    -> FROM (SELECT @RN:=0,@P:=0) RN,T2
    -> ORDER BY T2.VALUE,T2.ID
    -> ) S
    -> WHERE T1OBS >= T2OBS
    -> ) T2
    -> JOIN
    -> (
    -> SELECT T1.*,
    -> IF(T1.VALUE <> @P,@RN:=1,@RN:=@RN+1) RN,
    -> @P:=T1.VALUE P
    -> FROM (SELECT @RN:=0,@P:=0) RN,T1
    -> ORDER BY T1.VALUE,T1.ID
    -> ) T1
    -> ON T1.RN = T2.RN AND T1.VALUE = T2.VALUE
    -> WHERE T1.RN <= T2.T2OBS
    -> ORDER BY T1.VALUE,T1.ID;
+------+------+-------+
| ID   | ID   | VALUE |
+------+------+-------+
|    3 |    1 |     5 |
|    5 |    4 |     5 |
|    1 |    5 |    10 |
+------+------+-------+
3 rows in set (0.00 sec)