日度归档:2018年4月9日

mssql sqlserver 联结表简介


摘要:
下文将讲述sql中联结表的生成方法
联结表:是将多张表组合为一个结果集合
联结表需要采用select 脚本对表进行关联,将多个表返回一组集合输出,我们这种操作方式称之为”联结表”。
内连接 外连接 左连接 右连接都可以生成联结表


例:
使用select 做简单的连接-自然联结
select A.A,A.B,B.C,B.D from A , B where A.B=B.B 普通连接 效果等同于内连接
———————————————–
select * from A,B —-输出A表和B表的所有组合
———————————————-
内联结(又称之为内连接)
SELECT A.A, A.B, B.C
FROM A INNER JOIN B
ON A.keyId = B.keyId;
——————————————
联结多个表
SELECT A.A, A.B, B.C,C.D
FROM A, B, C
WHERE A.keyId = B.keyId
AND B.Name = C.Name
AND A.Qty = 100;
——————————————–
–联结查询
SELECT A.A, A.B, B.C,C.D
FROM A, B, C
WHERE A.keyId = B.keyId
AND B.Name = C.Name
AND A.Qty = 100;
——————————————–
自联结
例:获取一个递增结余的自联结

select t1.keyId,t1.qt_date,t1.qt_dkfs,t1.qt_srMoney,t1.qt_zcMoney,
sum(t2.qt_srMoney-t2.qt_zcMoney) as [结余数]
from cteName t1,cteName t2
where t1.keyId>=t2.keyId
group by t1.qt_date,t1.qt_dkfs,t1.qt_srMoney,t1.qt_zcMoney,t1.keyId
——————————————–
左联结
显示左表所有行数据,需要使用RIGHT OUTER JOIN,如下所示:
SELECT A.A,A.B,B.C
FROM A left OUTER JOIN B
ON A.keyId = b.keyId;

——————————————–
右联结
显示右表所有行数据,需要使用RIGHT OUTER JOIN,如下所示:
SELECT A.A,A.B,B.C
FROM A RIGHT OUTER JOIN B
ON A.keyId = b.keyId;
——————————————–
— 全外联结
显示左表和右表全部组合数据
SELECT A.A,A.B,B.C
FROM A FULL OUTER JOIN B
ON A.keyId = b.keyId;

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_子查询汇总数获取

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_子查询过滤数据应用简介