Sql学习第一天——SQL 练习题(建表/sql语句)
程序员文章站
2023-11-20 20:32:34
题目:来自madrid且订单数少于3的消费者
建表:复制代码 代码如下: set nocount on --当 set nocount 为 on...
题目:来自madrid且订单数少于3的消费者
建表:
复制代码 代码如下:
set nocount on --当 set nocount 为 on 时，不返回计数（表示受 transact-sql 语句影响的行数）。当 set nocount 为 off 时，返回计数
use sy
go
if object_id('dbo.orders') is not null
drop table dbo.orders
go
if object_id('dbo.customers') is not null
drop table dbo.customers
go
create table dbo.customers
(
customerid char(5) not null primary key ,
city varchar(10) not null
);
insert into dbo.customers values('fissa','madrid');
insert into dbo.customers values('frndo','madrid');
insert into dbo.customers values('krlos','madrid');
insert into dbo.customers values('mrphs','zion');
create table dbo.orders
(
orderid int not null primary key ,
customerid char(5) null references customers(customerid)
)
insert into dbo.orders values(1,'frndo');
insert into dbo.orders values(2,'frndo');
insert into dbo.orders values(3,'krlos');
insert into dbo.orders values(4,'krlos');
insert into dbo.orders values(5,'krlos');
insert into dbo.orders values(6,'mrphs');
insert into dbo.orders values(7,null);
------------------------------------------------------------------------------------------------------------------------------
做题分析：
复制代码 代码如下:
select customerid as 消费者,count(customerid) as 订单数
from dbo.orders
where customerid in (
select customerid
from dbo.customers
where city = 'madrid')
group by customerid
having count(customerid) < 3
结果如图所示：
--第一次想到的答案,突然发现少了一个来自madrid的fissa订单,fissa订单数量为0,所以在orders表中没有出现,所以上面的写法会少一个.
--推翻了上面的答案,又想到了用表的连接,而用内连接出现的情况会和上面的一样,所以我选择了左连接,如下:
复制代码 代码如下:
select c.customerid as 消费者,count(o.customerid) as 订单数
from dbo.customers as c left join dbo.orders as o on c.customerid = o.customerid
where c.city= 'madrid'
group by c.customerid
having count(c.customerid) < 3
结果如图所示：
--查询发现是正确的。
--分析查看不带条件的左连接
复制代码 代码如下:
select * from dbo.customers as c left join dbo.orders as o on c.customerid = o.customerid
复制代码 代码如下:
select * from dbo.customers as c left join dbo.orders as o on c.customerid = o.customerid
结果如图所示：
--书中给的标准答案是：
复制代码 代码如下:
select c.customerid , count(o.orderid) as numorders
from dbo.customers as c left join dbo.orders as o on c.customerid = o.customerid
where c.city= 'madrid'
group by c.customerid
having count(o.orderid) < 3
order by numorders
结果如图所示：
--书中给的只是多了一个order by 进行定义了排序方式(以numorders这一列的升序进行排序)