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