SQL学习之学会使用子查询
1、SELECT语句是SQL的查询。我之前的随笔中所用的SELECT语句都是简单的查询,即从单个数据库表中检索数据的单条SELECT语句。
查询:任何SQL语句都是查询,但此术语一般指SELECT语句。
2、SQL不仅允许简单的SELECT查询,还允许创建子查询,即嵌套在其他查询中的查询。
下面通过实例来了解子查询在实际项目中的应用:
create database Study
go
use Study
go
create table Customers(
Id int identity(1,1),
Name varchar(10) null
)
insert into Customers values('张三')
insert into Customers values('李四')
insert into Customers values('王五')
insert into Customers values('赵六')
insert into Customers values('冯七')
select * from Customers
create table Products(
Id int identity(1,1),
Name varchar(255) null
)
insert into Products values('产品一')
insert into Products values('产品二')
insert into Products values('产品三')
insert into Products values('产品四')
insert into Products values('产品五')
select * from Products
create table Orders(
Id int identity(1,1),
CustomerId int null,
AddDate datetime null
)
insert into Orders values(1,GETDATE())
insert into Orders values(2,GETDATE())
insert into Orders values(3,GETDATE())
insert into Orders values(4,GETDATE())
insert into Orders values(5,GETDATE())
select * from Orders
create table OrderItems
(
Id int identity(1,1),
OrderId int null,
ProductId int null
)
insert into OrderItems values(1,1)
insert into OrderItems values(1,2)
insert into OrderItems values(1,3)
insert into OrderItems values(2,3)
insert into OrderItems values(3,2)
insert into OrderItems values(4,1)
select * from OrderItems这是分析SQL子查询所需要用到的sql文件。
这是4个表的基础数据
需求:现在我们需要列出订购产品Id为1的所有顾客，下面是基本的思路:
(1)先去OrderItems(中间表)检索产品Id为'1'的所有的订单编号，代码如下:
select Id,orderId from OrderItems where ProductId=1
(2)拿到(1)步骤所有的订单Id编号之后,再根据检索出来的订单Id,去Orders(订单表)查找对应的顾客Id(是那些顾客下了这些订单)，代码如下:
select CustomerId from Orders where Id in (select orderId from OrderItems where ProductId=1)注意下面这种写法是错的:
select CustomerId from Orders where Id in (select Id,orderId from OrderItems where ProductId=1)这就相当于判断Id==Id,orderId这种写法是错的！
DMBS控制台报的错误信息:当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。告诉你子查询中的选择列表只能指定一个表达式!
(3)拿到所有顾客Id之后,再根据这些顾客Id,去Customers(顾客表)中检索顾客的详细信息,代码如下:
select * from Customers Where Id in (select CustomerId from Orders where Id in (select orderId from OrderItems where ProductId=1))
ok！完成了上面提出的需求
但是有一点不足的是如果查询的深度很深,代码就会很长,像上面的书写代码的方式,不易于我们阅读,有如下代码：
select *
from Customers
Where Id IN(select CustomerId
from Orders
where Id IN(select orderId
from OrderItems
where ProductId=1))这段代码层次分明,比之前的代码更加的容易理解!
3、下面是使用子查询必须知道的几点:
(1)很重要的一点,作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误(上面列子中的第(2)步已给出证明)。
(2)子查询的性能：上面给出的列子中的代码有效,并且获得了所需的结果。但是使用子查询并不总是执行这类数据检索最有效的方法。
所以，这里我使用内联结的方式完成上面所提出的需求，代码如下:
select * from
dbo.Customers a INNER JOIN
dbo.Orders b ON a.Id=b.CustomerId INNER JOIN
dbo.OrderItems c ON c.OrderId=b.Id
where c.ProductId=1
这里我的表数据有点变动，所以只看代码的逻辑即可！
4、下面改变当前随笔2中的需求,需要显示Customers表中的每个顾客的订单总数。
简单分析下思路,因为Customers表中没有订单总数这个字段,所以目前我们只能用一个计算字段来代替订单总数,而这个计算字段的列值必须是每个顾客的订单总数。
(1)先从Customers表中检索出顾客列表Id
(2)对于检索出来的每个顾客，统计其在Orders表中的订单数目
一般情况下统计一个顾客在Orders(订单表)中的订单总数,可以这样做:
select count(*) from Orders where CustomerId=1上面代码统计了顾客编号为1的订单总数,但是这里需要统计的是所有用户的订单总数,显然这样写不对,我们可以这样写:
select name,(select COUNT(*) from Orders where Orders.CustomerId=Customers.Id) as orders from Customers这段代码实际并不能检索出来结果,因为他将一个计算字段,被插入到其他的SELECT语句中一起被返回,该查询对对检索出的每个顾客ID执行一次 select COUNT(*) from Orders where dbo.Orders.CustomerId=当前顾客Id的运算;因为有5个顾客,所以会进行5次运算。返回每个顾客的总订单数。
下面是完整代码:
select name,(select COUNT(*) from Orders where Orders.CustomerId=Customers.Id) as orders from Customers子查询中的WHERE子句（where Orders.CustomerId=Customers.Id）与前面使用的WHERE子句稍有不同,因为它使用了完全限定列名,而不只是列名。他指定列名和表名(Orders.CustomerId和Customers.Id),这个Where子句等于告诉SQL比较Orders的CustomerId和当前正从Customers表中检索的Id
