mysql - 不使用 JOIN 查询多个数据库表？

Question

我试图将三个查询合并为一个查询，但不幸失败了。一切顺利，直到我添加“order by”子句。

SELECT
wti.price, hh.price, mb.price
FROM
spotprices.wti, spotprices.hh, spotprices.mb
ORDER BY
wti.sdate desc limit 1, hh.sdate desc limit 1, mb.sdate desc limit 1;

本质上，我试图通过使用“按 sdate desc limit 1 排序”技巧按 sdate 获取最新价格。单独地，查询运行良好。

SELECT price FROM spotprices.wti ORDER BY wti.sdate desc LIMIT 1;

Answer 1

在select中使用子查询:

SELECT (SELECT wti.price FROM spotprices.wti ORDER BY wti.sdate DESC LIMIT 1) as wti_price,
       (SELECT hh.price FROM spotprices.hh ORDER BY hh.sdate DESC LIMIT 1) as hh_price,
       (SELECT mb.price FROM spotprices.mb ORDER BY mb.sdate DESC LIMIT 1) as mb_price;