mysql嵌套查詢和非嵌套查詢對比
2021-09-15 15:20:12

SELECT
    CONCAT(
        "【",
        device_.`name`,
        "】",
        info_.`name`
    ) `name`,
    history_.val,
    history_.created_date
FROM
    b_analoginfo info_
LEFT JOIN b_systemdeviceinfo device_ ON device_.id = info_.device_id
LEFT JOIN t_analogtype type_ ON type_.id = info_.analog_type_id
INNER JOIN h_analog_value_history history_ ON device_.devicesn = history_.devicesn
AND type_.analog_code = history_.analog_code
AND type_.channel * (info_.address - 1) + info_.channel = history_.val_channel

SELECT
        CONCAT(
            "【",
            info_.device_name,
            "】",
            info_.`name`
        ) `name`,
        history_.val,
        history_.created_date
    FROM
        h_analog_value_history history_
    INNER JOIN (
        SELECT
            device_.`name` device_name,
            info_.`name`,
            device_.devicesn,
            type_.analog_code,
            type_.channel * (info_.address - 1) + info_.channel val_channel
        FROM
            b_analoginfo info_
        LEFT JOIN b_systemdeviceinfo device_ ON device_.id = info_.device_id
        LEFT JOIN t_analogtype type_ ON type_.id = info_.analog_type_id
    ) info_ ON info_.devicesn = history_.devicesn
    AND info_.analog_code = history_.analog_code
    AND info_.val_channel = history_.val_channel