mssql sqlserver 利用子查询进行数据过滤方法分享


摘要:
下文将举例说明使用子查询的方式对数据进行过滤


例:

  /*创建基础数据*/
 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 in ('洗衣粉')  
 ---例2:获取 所有商品的ID编号
 select name,(select top 1  keyId from test2 where test2.name=test.name) from test

 go
 truncate table test 
 drop table test
 go
 truncate table test2
 drop table test2 
mssql_sqlserver_子查询过滤数据应用简介

mssql_sqlserver_子查询过滤数据应用简介