标签归档:all

mssql sqlserver 2008中all any some关键字应用举例说明


摘要:
下文讲述sqlserver 2008中 all any some关键字的应用举例说明
实验环境:sqlserver 2008


all:对子查询中所有结果都满足条件时才返回
any/some:对子查询中任意值满足条件时则返回行记录

create table A(keyId int,info varchar(88))
create table B(keyId int)
insert into A(keyId,info)values(1,'a'),
(2,'b'),(3,'c')
insert into B(keyId)values(1),(2)
---返回A表中keyId满足子查询中任意条件的结果
select * from A where keyId > any(select keyId from B)
---返回A表中keyId满足子查询中任意条件的结果
select * from A where keyId > some(select keyId from B)
---返回A表中keyId满足子查询中所有条件的结果
select * from A where keyId > all(select keyId from B)

truncate table A
drop table A 
truncate table B
drop table B
mssql_sqlserver_any_all_some关键字应用

mssql_sqlserver_any_all_some关键字应用

mssql sqlserver 2008 中any、all关键字的应用简介


摘要:
下文将分享sql中关键字any all的用法,如下所示:
实验环境:sqlserver 2008 R2


any all关键字说明:

any、all关键字应用于子查询的比阿中,
any:同子查询中所有集合的任意一个进行比较,满足条件返回true
all: 同子查询中所有集合的所有进行比较 ,满足条件返回true

例:
create table A(keyId int,info varchar(88))
create table B(keyId int)
insert into A(keyId,info)values(1,'a'),
(2,'b'),(3,'c')
insert into B(keyId)values(1),(2)

select * from A where keyId > any(select keyId from B)
---查询A表中keyId大于B表中keyId的任意值就可以,所以只需大于最小值则可,类似下面的写法
 ---select * from A where keyId >(select min(keyed) from B)

select * from A where keyId > All(select keyId from B)
---查询A表中keyId大于B表中所有keyId的值就可以,所以需大于最大值则可,类似下面的写法
 ---select * from A where keyId >(select max(keyed) from B)


truncate table A
drop table A 
truncate table B
drop table B
mssql_sqlserver_any_all关键字用法

mssql_sqlserver_any_all关键字用法