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
 ---例:获取各种类型商品的销售数量总额和销售合计 
 select *,
 (select sum(qty) from test where test.name=test2.name) as [数量合计],
 (select sum(priceALl) from test where test.name=test2.name) as [金额合计]
  from [test2]
 go
 truncate table test 
 drop table test
 go
 truncate table test2
 drop table test2 
mssql_sqlserver_子查询汇总数获取

mssql_sqlserver_子查询汇总数获取