将 2 个选择语句重写为一个 JOIN 语句

Question

我一直在努力解决一个我无法弄清楚的 JOIN 语句。

我想在两个不同的表中获取最新(最新(条目，并在输出中连接结果。

2 Select 语句如下所示，它们按预期工作，但当我尝试将它们重新命名为 JOIN 语句时，我似乎无法获得正确的结果。

请帮忙，谢谢。

SELECT MaxNum 
FROM table1 
WHERE UserID = 4
ORDER BY Date DESC
LIMIT 1
SELECT MinNum 
FROM table2 
WHERE UserID = 4
ORDER BY Date DESC
LIMIT 1

Answer 1

我不确定在这里连接是否有意义。 但是，您可以稍微修改当前必须为一个查询的内容：

SELECT
    (SELECT MaxNum FROM table1 WHERE UserID = 4 ORDER BY Date DESC LIMIT 1) AS MaxNum,
    (SELECT MinNum FROM table2 WHERE UserID = 4 ORDER BY Date DESC LIMIT 1) AS MinNum

Answer 2

由于每个查询只返回一行，因此您可以使用的一个肮脏技巧是cross join结果：

SELECT MaxNum, MinNum
FROM   (SELECT   MaxNum 
        FROM     table1 
        WHERE    UserID = 4
        ORDER BY Date DESC
        LIMIT    1) t 
CROSS JOIN (SELECT   MinNum 
            FROM     table2 
            WHERE    UserID = 4
            ORDER BY Date DESC
            LIMIT    1) s

Answer 3

select TOP 1 table1.MaxNum, table2.MinNum 
FROM table1 INNER JOIN table2 
ON table1.UserID = table2.UserID 
WHERE table1.UserID = 4 
ORDER BY table1.Date DESC

Answer 4

如果要将结果显示为一行，请将查询用作子句FROM子查询。

SELECT *
FROM 
  (SELECT MaxNum FROM table1 WHERE UserID = 4 ORDER BY `Date` DESC LIMIT 1) AS q1,
  (SELECT MinNum FROM table2 WHERE UserID = 4 ORDER BY `Date` DESC LIMIT 1) AS q2;