mssql sqlserver 子查询用法举例分享


摘要:
下文举例讲述三种子查询的用法,
子查询可作为条件,也可以作为数据源使用



子查询定义:

把一个查询放入另一个查询中,我们把这种操作方式称之为子查询。
—————-

子查询分类:

根据返回信息不同分为以下几类:
1:单值子查询 返回一个值
2:列子查询 返回多行单列
3:表子查询 返回一张表
子查询中常见的操作符为:in >= > < <= exists 例:
   /*创建基础数据*/
 create table test(
 name varchar(30), sort varchar(30),
 qty int,priceAll int
 )
 create table test2(name varchar(30),KeyId int primary key);
 go
 /*生成原始数据*/
 insert into test2(name,keyId)values(‘洗衣粉’,1),(‘洗发水’,2),
 (‘肥皂’,3),(‘牙刷’,4),(‘洗衣粉’,5)
 go
 insert into test (name,sort,qty,priceAll)values(‘洗衣粉’,’a’,100,1200)
 insert into test (name,sort,qty,priceAll)values(‘洗发水’,’a’,90,800)
 insert into test (name,sort,qty,priceAll)values(‘肥皂’,’b’,60,100)
 insert into test (name,sort,qty,priceAll)values(‘牙刷’,’c’,30,50)
 insert into test (name,sort,qty,priceAll)values(‘洗发水’,’a’,60,160)
 insert into test (name,sort,qty,priceAll)values(‘肥皂’,’b’,70,120)
 insert into test (name,sort,qty,priceAll)values(‘牙刷’,’c’,10,20)
 go
  
—1.单值子查询
select name,sort,qty,priceAll from test
     where name=(select name from [test2] where keyId=2) 

—- 2.列子查询
       select name,sort,qty,priceAll from test
        WHERE name in ( SELECT name FROM [test2] WHERE keyId <=2)
       
 ----3.表子查询
        select name,sort,qty,priceAll 
          FROM (SELECT name,sort,sum(qty) as qty,sum(priceAll) as priceALl 
          FROM test group BY name,sort) as tmp

 go
 truncate table test 
 drop table test
 go
 truncate table test2
 drop table test2 
mssql_sqlserver_子查询用法简介

mssql_sqlserver_子查询用法简介