我的桌子上有这样的行

+----+-------+--------+--------------+---------------------+
| id | cid   | number | value        | date                |
+----+-------+--------+--------------+---------------------+
|  2 |     1 |     55 | two to one   | 2020-04-19 11:25:52 |
|  2 |     9 |     56 | two to nine  | 2020-04-19 11:26:04 |
|  1 |     2 |     57 | one to two   | 2020-04-19 11:27:02 |
|  9 |     2 |     58 | nine to two  | 2020-04-19 11:28:01 |
+----+-------+--------+--------------+---------------------+

像这样显示的sql代码是什么

+----+-------+--------+--------------+---------------------+
| id | cid   | number | value        | date                |
+----+-------+--------+--------------+---------------------+
|  1 |     2 |     57 | one to two   | 2020-04-19 11:27:02 |
|  9 |     2 |     58 | nine to two  | 2020-04-19 11:28:01 |
+----+-------+--------+--------------+---------------------+

就是说只显示最新行，使id与cid相同，就像id=1-cid=2与id=2-cid=1一样。任何人都请帮助，我希望你知道我的意思。非常感谢

分析解答

您可以使用派生表使用LEAST和GREATEST将(1, 2)映射（例如）到(2, 1)，从而为id和cid值的每种组合生成最新日期值的列表。然后可以将它JOINed到原始表中以获取最新日期的数据：

SELECT t1.id, t1.cid, t1.number, t1.value, t1.date
FROM data t1
JOIN (
  SELECT LEAST(id, cid) AS l_id, 
         GREATEST(id, cid) AS g_id,
         MAX(date) AS max_date
  FROM data
  GROUP BY l_id, g_id
) t2 ON t2.max_date = t1.date
    AND (t2.l_id = t1.id AND t2.g_id = t1.cid OR
         t2.l_id = t1.cid AND t2.g_id = t1.id)

输出（用于样本数据）：

id  cid     number  value           date
1   2       57      one to two      2020-04-19T11:27:02Z
9   2       58      nine to two     2020-04-19T11:28:01Z

关于SQLFiddle的demo