sql - 内连接 FROM 子句中的重复表名 (csuser.t2)

Question

下面是抛出错误的查询:作为内部联接的 FROM 子句中的重复表名 (csuser.t2)

SELECT  t1.rxno, 
        t1.txno, 
        t2.create_datetime FROM    mrgrxtxt t1,rx t2
    INNER JOIN      (
        SELECT   rxno, 
               t1. MIN(txno) AS min_txno,
               t2. MAX(create_datetime) AS latest_txno
        FROM    mrgrxtxt t1 , rx t2
        GROUP BY 1
    ) t2 ON t1.rxno = t2.rxno WHERE   t2.min_txno > 8
        AND t2.create_datetime = t2.latest_txno;

有人可以帮忙吗？？这是 informix 数据库。

Answer 1

您已经定义了两个别名 t2，一个用于 rx，另一个用于子查询。尝试将第二个 t2 更改为 t3 之类的其他内容。

SELECT  t1.rxno, 
        t1.txno, 
        t2.create_datetime 
    FROM    mrgrxtxt t1
    JOIN rx t2 ON t1.rxno = t2.rxno
    INNER JOIN(
        SELECT rxno, 
               t1. MIN(txno) AS min_txno,
               t2. MAX(create_datetime) AS latest_txno
        FROM mrgrxtxt t1 
        JOIN rx t2 ON t1.rxno = t2.rxno
        GROUP BY 1
    ) t3 ON t1.rxno = t3.rxno 
    WHERE   t3.min_txno > 8 AND t2.create_datetime = t3.latest_txno;