与mysql的特定连接

c3frrgcw  于 2021-06-19  发布在  Mysql
关注(0)|答案(4)|浏览(276)

这是我要运行的查询:

SELECT
    o.id,
    history.name,
    history.id AS historyId
FROM
    order_sheet AS o
LEFT JOIN(
    SELECT
        h.detail,
        h.eid,
        h.date_change
    FROM
        order_history AS h
    WHERE
        h.id_order_sheet = o.id
    ORDER BY
        h.id
    DESC
    LIMIT 0, 1
) history
ON
    history.id_order_sheet = o.id
ORDER BY
    o.id
LIMIT 0, 150;

我的问题来自第二选择。它不知道o.id。
你知道怎么做吗?
我会解释我该做什么:)
我想选择表order\u history中插入的最后一个元素,其值为order\u history.id\u order\u sheet===to order\u sheet.id
谢谢你的帮助

70gysomp

70gysomp1#

你可以在下面试试

SELECT
    o.id,
    history.name,
    history.id AS historyId
FROM
    order_sheet AS o
LEFT JOIN(
    SELECT
        h.detail,
        h.eid,
        h.date_change
    FROM
        order_history AS h join order_sheet o1
    on
        h.id_order_sheet = o1.id
    ORDER BY
        h.id
    DESC
    LIMIT 0, 1
) history
ON
    history.id_order_sheet = o.id
ORDER BY
    o.id
LIMIT 0, 150;
ovfsdjhp

ovfsdjhp2#

您要做的是一个横向连接,mysql还不支持。
但是,要获得每个订单表id的最新历史记录并不困难。您不需要联接。

select id_order_sheet, name, id as history_id
from order_history
where id in (select max(id) from order_history group by id_order_sheet)
order by id_order_sheet;
7y4bm7vi

7y4bm7vi3#

显然我不能测试这个,但有一种方法:

SELECT
    o.id,
    h.name,
    h.id AS history_id,
    h.detail,
    h.eid,
    h.date_change
FROM order_sheet o
LEFT JOIN order_history h ON h.id_order_sheet = o.id
WHERE h.id IN (
    SELECT MAX(id)
    FROM order_history
    GROUP by id_order_sheet
);
kuhbmx9i

kuhbmx9i4#

我们可以使用非相关子查询重写您的联接:

SELECT
    o.id,
    h2.name,
    h2.id AS historyId
FROM order_sheet AS o
LEFT JOIN
(
    SELECT id_order_sheet, MAX(id) AS max_id
    FROM order_history
    GROUP BY id_order_sheet
) h1
    ON o.id = h1.id_order_sheet
INNER JOIN order_history h2
    ON h1.id_order_sheet = h2.id_order_sheet AND h1.id = h2.max_id
ORDER BY
    o.id
LIMIT 0, 150;

相关问题