mysql - 如何从 join 2 表中获取 1 行，其中 a.user_id = 'aa' 并按上次排序

Question

我有 2 个表，例如

TABEL_AA

id   uid   user_id   name   date
1    A11      aa     BBBB   2017-01-01 00:01:01
2    A13      aa     cccc   2017-01-01 00:01:05

TABEL_BB(临时)

id   uid   user_id   name   date
1    A12      aa     BBBB   2017-01-01 00:01:02
2    A14      aa     cccc   2017-01-01 00:01:08

从 2 表中我得到 1 最后一行，其中 user_id = 'aa' 和 ORDER BY date DESC

结果来自TABEL BB

id   uid   user_id   name   date
2    A14      aa     cccc   2017-01-01 00:01:08

反之亦然..如果最后一条记录在 TABEL AA 中

我使用此查询但总是收到错误:

"SELECT 
    'a.uid' 
 FROM TABEL_AA a 
    LEFT JOIN TABLE_BB b ON a.user_id = b.user_id 
 WHERE a.user_id = 'aa' LIMIT 1 ORDER BY date DESC";

错误:

"Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY date DESC' at line 1"

谢谢你的帮助..

Answer 1

您需要在排序之前连接表，并使用限制:

select * from table_aa union select * from table_bb
where `user_id` = 'aa'
order by date_whatever desc
limit 1

我已将您的 date 列重命名为 date_whatever，因为 date 是 MariaDB 中的保留字。

这是一个SQLFiddle尝试这个答案