SQL server 自己比较自己

数据表

1.png

写查询语句
语名一

SELECT DISTINCT 
t.*
FROM  orderitems AS t, orderitems AS s
WHERE t.item_price > s.item_price

查询结果是最小值被过滤掉
语句二

SELECT DISTINCT 
s.*
FROM  orderitems AS t, orderitems AS s
WHERE t.item_price > s.item_price

查询结果，如下，最大值 被过滤掉

语句三

SELECT DISTINCT
T.*
FROM  orderitems AS t, orderitems AS s
WHERE t.item_price > s.item_price 
    and  s.order_num='20005'

语句四

SELECT DISTINCT 
s.*
FROM  orderitems AS t, orderitems AS s
WHERE t.item_price > s.item_price 
    and  s.order_num='20005'

2.png

总结： 这种写法 ，可以过滤 比任一条件大或小的值 。

延伸一种用法，可以过滤掉最大值或最小值 。