疑问解惑，已解决，11g以前的版本不支持二级子查询参数使用最外层表字段

王火火

于 2020-12-02 09:15:40 发布

阅读量643

点赞数

本文链接：https://blog.csdn.net/qq_38316721/article/details/110467677

版权

两个表用户信息 detection_users 和检测样本detection_sampling，1对多关联 s.users_id = t.id，要求查出detection_users在detection_sampling中的最新一条数据且 is_submit_state值为'1'的，符合条件的 detection_users的相关数据。

这个是在oracle11中的

SELECT
    *
FROM
    detection_users t
WHERE
        t.is_delete = 10000
    AND
        t.collectionlocale = '4662ebf544024c95a0eca437ef040883'
    AND
        t.user_state = 'submitted'
    AND (
        '1' IN (
            SELECT
                g.is_submit_state
            FROM
                (
                    SELECT
                        *
                    FROM
                        (
                            SELECT
                                s.*,
                                ROWNUM AS rn
                            FROM
                                detection_sampling s
                            WHERE
                                    s.users_id = t.id
                                AND
                                    s.is_delete = 10000
                            
                                    
                            ORDER BY s.create_time DESC
                        )
                    WHERE
                        rn = 1
                ) g
        )
    );

上面写法o11找不到第二层子查询的表对象，应该这样写

SELECT
    *
FROM
    (
        SELECT
            t.*,
            (
                SELECT
                    MAX(is_submit_state)
                FROM
                    detection_sampling
                WHERE
                        users_id = t.id
                    AND
                        is_delete = 10000
                    AND
                        create_time >= (
                            SELECT
                                MAX(create_time)
                            FROM
                                detection_sampling
                            WHERE
                                    users_id = t.id
                                AND
                                    is_delete = 10000
                        )
            ) AS sss,
            (
                SELECT
                    MAX(fill_in_state)
                FROM
                    detection_sampling
                WHERE
                        users_id = t.id
                    AND
                        is_delete = 10000
                    AND
                        create_time >= (
                            SELECT
                                MAX(create_time)
                            FROM
                                detection_sampling
                            WHERE
                                    users_id = t.id
                                AND
                                    is_delete = 10000
                        )
            ) AS fff
        FROM
            detection_users t
        WHERE
                t.is_delete = 10000
            AND
                t.collectionlocale LIKE :collectionlocale
            AND
                t.user_state LIKE :user_state
            AND
                TO_DATE(
                    TO_CHAR(t.update_time,'yyyy-MM-dd hh24:mi:ss'),
                    'yyyy-MM-dd hh24:mi:ss'
                ) >= TO_DATE('2021-01-19 00:00:00','yyyy-MM-dd hh24:mi:ss')
            AND
                TO_DATE(
                    TO_CHAR(t.update_time,'yyyy-MM-dd hh24:mi:ss'),
                    'yyyy-MM-dd hh24:mi:ss'
                ) <= TO_DATE('2021-02-02 23:59:59','yyyy-MM-dd hh24:mi:ss')
    ) us
WHERE
    1 = 1
ORDER BY
    us.sort DESC,
    us.update_time DESC,
    us.id DESC