前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >复杂一点的查询

复杂一点的查询

作者头像
liulun
发布2022-05-08 17:49:59
5580
发布2022-05-08 17:49:59
举报
文章被收录于专栏:liulunliulun

一:inner join inner join   是在做排除,如果任一行在两个表中不匹配,则注定将从最终的结果中排除掉 例子1:select * from employee e inner join employee m on e.managerid = m.employeeid 这是从一个表里查询了两次 得到的一行记录将包括两个employee的信息  前面的是经理  后面的是打工的 注意from哪个表  哪个表的信息就在前面 其中e和m分别是表的别名,这里的别名和列的别名不同,不用写as

例子2   select  e.employeeid,  ce.firstname,  m.employeeid as managerid,  cm.firstname as managerfirst   from employee e inner join  employee m on e.managerid = m.employeeid inner join contact ce on e.contactid = ce.contactid inner join contact cm on m.contactid = cm.contactid 第一个联接是把一个表与他自身进行连接  这个叫自引用(注意表的别名) 第二个连接得到经理的名字 第三个连接得到雇员的名字  看似很复杂的连接  其实很简单 最后说一点inner join 是默认的连接类型   inner 关键字是可选的 二:outer join 先看例子 select e.employeeid,   m.employeeid  as managerid from employee e left outer join employee m  on e.managerid = m.employeeid 无论左侧表(e)中的行是否与右侧表中的行相匹配都要显示 如果左侧表中的行在右侧表中找不到相匹配的数据,  那么右侧表的数据为null

right outer join 也类似   outer是可以忽略的 三:full  join  与  cross  join 这两个其实都不必多说 full  join  是  left join  和  right join 的结合 full  join将包含位于连接两侧的表的所有行 不存在的行就用null补齐

cross join  没有on操作符 得到的是两测表中所有行的  笛卡儿积 就是把两册的行排列组合一下 一般不会存在null的行 这是相当罕见的用法 只有科学家或者来搞样本数据的才会用到这个用法 四:union union更像是从一个查询直接向另一个查询进行的数据追加(差别还是有的) join更像是水平的合并数据(添加更多的列),union是垂直的合并数据(添加更多的行) 先看例子:select col2 from table1 union  all select col4 from table2 1:select 列表中字段的数量必须相同 2:字段的数据类型必须隐士兼容 3:返回的结果集的标头取自第一个查询 4:默认返回方式是distinct,union  alll返回全部的集合

五:子查询返回单个值 先看例子: declare @hits int select  @hits = min(hitnum) from articles select  distinct A.title from articles A join users U on A.uid = U.id where A.hitnum = @hits 这是完全可行的,但是我们可以用另一种形式: select distinct A.title from articles A join users U on U.id = A.uid where A.hitnum = (select min(hitnum) from articles ) 这就是子查询 六:子查询返回多个值 接着看例子(子查询里返回多个值) use  database select  A.title from articles A join users U on A.uid = U.id where A.id in (select id from articles where istop = 'true') 再看一个例子(用子查询找出孤立的记录) select  A.title from article A join users U on A.uid = U.id where A.id not in (select id from articles where istop = 'true') 这个例子写的有点牵强 但是这里注意  not in 子查询得到的字段  不能有null直 存在,如果有  那么整个句子将返回空 细心的人大概看出来了,前面提到的两个子查询的例子几乎都可以用内联(join)的方式替换掉 出于性能上的考虑,我们应该首先选择联结的解决方案  而不是子查询

七:any  some  和  all any与some功能上是相同的,推荐用some any与some一般都是和比较运算符一起使用的(>=  <=  <>  !> 等  ) >some (1,2,3)意味着大于1    >some就是大于最小的一个值 =some和in  起的作用是一样的 not in (a,b,c)展开为  <>a and  <>b and <>c <>some (a,b,c)展开为  <>a or <>b  or <> c >all (1,2,3)意味着大于3 八:where子句中的相关子查询(外部查询和内部查询) 先看个例子

代码语言:javascript
复制
use adventureworks
drop table #MinOrderdates

select CustomerID, min(OrderDate)as orderdate
into #MinOrderdates
from Sales.SalesOrderHeader
group by CustomerID
order by CustomerID

select o.CustomerID,o.SalesOrderID,o.OrderDate
from Sales.SalesOrderHeader o
join #MinOrderdates t
on o.CustomerID = t.CustomerID
and o.OrderDate = t.orderdate
order by o.CustomerID
drop table #MinOrderdates

每个顾客在系统中的第一张定单的orderid 和orderdate 用到了临时表,执行了两次查询 看下面的例子

代码语言:javascript
复制
select o1.CustomerID,o1.SalesOrderID,o1.OrderDate
from Sales.SalesOrderHeader o1
where o1.OrderDate =
(
select min(o2.OrderDate)
from Sales.SalesOrderHeader o2
where o2.CustomerID = o1.CustomerID
)
order by CustomerID

执行了一次查询 注意内部查询对外部查询有一个显示的引用  o2.CustomerID = o1.CustomerID 当然外部查询也可以引用内部查询中的列 第二个例子

代码语言:javascript
复制
select c.LastName,
(
select min(OrderDate) 
from Sales.SalesOrderHeader o
where o.ContactID = c.ContactID
) as orderdate
from Person.Contact c

九:派生表

先看例子:  订购过某种特定产品的顾客列表

代码语言:javascript
复制
use AdventureWorks;
select c.FirstName,c.LastName
from Person.Contact As c
join Sales.SalesOrderHeader as soh
on c.ContactID = soh.ContactID
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID = sod.SalesOrderID
join Production.Product as p
on sod.ProductID =p.ProductID
where p.Name = 'Minipump'

查找顾客的名字以及首次订购的日期

90行受影响 现在我们想查找即订购过Minipump又订购过AWC Logo Cap的顾客 如果最后是 where p.Name = 'Minipump' or p.Name = 'AWC Logo Cap' 2412行受影响 这样做是错误的 这样得到的结果是订购过Minipump和订购过AWC Logo Cap的顾客 下面来看我们的解决方法 

代码语言:javascript
复制
use AdventureWorks
select distinct c.FirstName,c.LastName
from Person.Contact As c
join 
(
select ContactID from Sales.SalesOrderHeader As soh
join Sales.SalesOrderDetail as sod
    on soh.SalesOrderID = sod.SalesOrderID
join Production.Product As p
    on sod.ProductID = p.ProductID
    where p.Name = 'Minipump' 
) pumps
on c.ContactID = pumps.ContactID
join 
(
select ContactID from Sales.SalesOrderHeader as soh
join Sales.SalesOrderDetail as sod
    on soh.SalesOrderID = sod.SalesOrderID
join Production.Product as p
    on sod.ProductID = p.ProductID
    where p.Name = 'AWC Logo Cap' 
) caps
on c.ContactID = caps.ContactID

把select出的内容当成一个表用 这就是派生表

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2008-12-14,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档