7、all some any的使用.sql

来自「本代码简单明了」· SQL 代码 · 共 56 行

--使用any ,some ,all　修改的比较运算符

----ALL       在payterms字段里,所有'net 30 '取出最大的值
select * from sales
where qty > all
(select qty from sales where payterms = 'net 30')

----ANY        先找出net 30相等的字段,找出里面最小的值,在最终拿最小值比较原表里的值,谁比最小值大就显示出来

select * from sales
select qty from sales where payterms = 'net 30'

select * from sales
where qty > any
(select qty from sales where payterms = 'net 30')

----SOME
select * from sales
where qty > some
(select qty from sales where payterms = 'net 30')


----ANY,NOT IN,ALL
select * from sales
where qty <> any
(select qty from sales where payterms = 'net 30')

select * from sales
where qty not in
(select qty from sales where payterms = 'net 30')

select * from sales
where qty <> ALL
(select qty from sales where payterms = 'net 30')



--ALL
SELECT title
FROM titles
WHERE advance > ALL
   (SELECT advance
    FROM publishers INNER JOIN titles ON 
      titles.pub_id = publishers.pub_id
    WHERE pub_name = 'Algodata Infosystems'
	and advance is not null
   )

--从publishers和titles表中查询pub_naem='ALG...'
--并且预付款不是空的　预付款１　的数目。

--在titles表中查找预付款 > 所有的　预付款１
----------------------------------------------------------------------------

--SOME