带有变量的子查询在哪里

我不知道为什么，但是如果我subquery首先LEFT JOIN使用变量的值执行 where@id它不会返回任何结果，就好像 的值@id始终为空。

但是为什么不LEFT JOIN直接做呢？

很简单，表很大，查询执行起来很麻烦，所以它会快得多。

SELECT
    DISTINCT @id := p.id_pen,
    p.id_env_pen,
   
    0 AS id_star,
    FALSE AS is_star,
       
    u.id_user AS sender_id,
    u.name_user AS sender_name,

    u2.id_user AS response_id,
    u2.name_user AS response_name,
       
    p.text_pen AS text_send,
    p2.text_pen AS text_response,
       
    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,
       
    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
    LEFT JOIN (
        SELECT 
            p2.id_pen,
            p2.text_pen,    
            p2.created_at_pen,
            p2.id_pen_req_pen
        FROM pendency AS p2
        WHERE p2.id_pen_req_pen = @id 
        # HERE, I try do `= p.id_pen` but dont works too, 
        # id not identify the alias from outside
    ) AS p2 ON p2.id_pen_req_pen = @id
    
    INNER JOIN users AS u ON u.id_user = p.id_user_pen
    LEFT JOIN users AS u2 ON u2.id_user = p.id_user_req_pen
WHERE (p.id_user_pen = '5' AND p.id_user_req_pen != -10)
OR p.id_user_req_pen = '5'

创建表

CREATE TABLE IF NOT EXISTS `users` (
    `id_user` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `id_fs_user` VARCHAR(30) DEFAULT 'Não informado',
    `email_user` VARCHAR(105) DEFAULT 'Não informado',
    `pass_user` VARCHAR(35) DEFAULT 'Não informado',
    `name_user` VARCHAR(120) DEFAULT 'Não informado',
    `cpf_user` VARCHAR(15) DEFAULT 'Não informado',
    `type_user` VARCHAR(15) DEFAULT 'master',
    `work_week_user` INT DEFAULT 5,
    `is_block_user` BOOLEAN DEFAULT FALSE,
    `permission_id_user` INT NOT NULL DEFAULT 0,
    `attendant_user` BOOLEAN DEFAULT FALSE,
    `version_id_user` INT NOT NULL DEFAULT 0,
    `last_login_user` DATETIME,
    `last_move_user` DATETIME,

    `id_user_user` INT DEFAULT 0,
    `created_at_user` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `safe_del_date_user` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `safe_del_state_user` BOOLEAN DEFAULT FALSE,
    `safe_del_user_user` INT DEFAULT 0);

CREATE TABLE IF NOT EXISTS `pendency` (
    `id_pen` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `id_env_pen` INT NOT NULL,
    `text_pen` TEXT,
    `id_user_req_pen` INT DEFAULT 0,
    `id_pen_req_pen` INT DEFAULT 0,
    `time_res_pen` INT NOT NULL,
    `state_pen` BOOLEAN DEFAULT FALSE,
    `id_old_pen` INT NOT NULL,
    `id_user_pen` INT NOT NULL,
    `created_at_pen` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`id_env_pen`) REFERENCES `env`(`id_env`) ON DELETE CASCADE);