github.com/matrixorigin/matrixone@v1.2.0/pkg/sql/plan/tpch/q21.sql (about)

     1  select
     2  	s_name,
     3  	count(*) as numwait
     4  from
     5  	supplier,
     6  	lineitem l1,
     7  	orders,
     8  	nation
     9  where
    10  	s_suppkey = l1.l_suppkey
    11  	and o_orderkey = l1.l_orderkey
    12  	and o_orderstatus = 'F'
    13  	and l1.l_receiptdate > l1.l_commitdate
    14  	and exists (
    15  		select
    16  			*
    17  		from
    18  			lineitem l2
    19  		where
    20  			l2.l_orderkey = l1.l_orderkey
    21  			and l2.l_suppkey <> l1.l_suppkey
    22  	)
    23  	and not exists (
    24  		select
    25  			*
    26  		from
    27  			lineitem l3
    28  		where
    29  			l3.l_orderkey = l1.l_orderkey
    30  			and l3.l_suppkey <> l1.l_suppkey
    31  			and l3.l_receiptdate > l3.l_commitdate
    32  	)
    33  	and s_nationkey = n_nationkey
    34  	and n_name = 'BRAZIL'
    35  group by
    36  	s_name
    37  order by
    38  	numwait desc,
    39  	s_name
    40  limit 100
    41  ;