SQL：找到一个关于all some any的用法，可在SSMS里看效果

SET nocount ON

USE tempdb
go

IF ( OBJECT_ID(‘t1‘) IS NOT NULL )
DROP TABLE t1
CREATE TABLE t1 ( n INT )
INSERT INTO t1
SELECT 2
UNION
SELECT 3

IF ( OBJECT_ID(‘t2‘) IS NOT NULL )
DROP TABLE t2
CREATE TABLE t2 ( n INT )
INSERT INTO t2
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4

-- t1表数据 2,3
-- t2表数据 1,2,3,4
SELECT *
FROM t1
SELECT *
FROM t2
-- ‘>all‘ 表示:t2表中列n的数据大于t1表中列n的数据的数，结果只有4.
SELECT *
FROM t2
WHERE n > ALL ( SELECT n
FROM t1 )
--4
SELECT *
FROM t2
WHERE n > ANY ( SELECT n
FROM t1 )
--3,4
SELECT *
FROM t2
WHERE n > SOME ( SELECT n
FROM t1 )
--3,4

SELECT *
FROM t2
WHERE n = ALL ( SELECT n
FROM t1 )
--无数据
SELECT *
FROM t2
WHERE n = ANY ( SELECT n
FROM t1 )
--2,3
SELECT *
FROM t2
WHERE n = SOME ( SELECT n
FROM t1 )
--2,3

SELECT *
FROM t2
WHERE n < ALL ( SELECT n
FROM t1 )
--1
SELECT *
FROM t2
WHERE n < ANY ( SELECT n
FROM t1 )
--1,2
SELECT *
FROM t2
WHERE n < SOME ( SELECT n
FROM t1 )
--1,2

SELECT *
FROM t2
WHERE n <>ALL ( SELECT n
FROM t1 )
--1,4
SELECT *
FROM t2
WHERE n <>ANY ( SELECT n
FROM t1 )
--1,2,3,4
SELECT *
FROM t2
WHERE n <>SOME ( SELECT n
FROM t1 )
--1,2,3,4
SET nocount OFF

SQL：找到一个关于all some any的用法，可在SSMS里看效果,布布扣,bubuko.com