TPC-H q17查询SQL：

snippet.sql

SELECT
        SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
        lineitem,
        part
WHERE
        p_partkey = l_partkey
        AND p_brand = 'Brand#23'
        AND p_container = 'MED BOX'
        AND l_quantity < (
                SELECT
                        0.2 * avg(l_quantity)
                FROM
                        lineitem
                WHERE
                        l_partkey = p_partkey
        );

等价改写为：

snippet.sql

SELECT
        SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
        lineitem,
        part
WHERE
        p_partkey = l_partkey
        AND p_brand = 'Brand#23'
        AND p_container = 'MED BOX'
        AND l_quantity < (
                SELECT
                        0.2 * avg(l_quantity)
                FROM
                        lineitem
                WHERE
                        l_partkey IN 
                        (SELECT p_partkey FROM part WHERE p_brand = 'Brand#23' AND p_container = 'MED BOX')
                        AND 
                        l_partkey = p_partkey
        );

打赏作者以资鼓励：