欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Sql学习第一天——SQL 练习题(建表/sql语句)

程序员文章站 2023-12-10 23:48: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

结果如图所示:

Sql学习第一天——SQL 练习题(建表/sql语句)

--第一次想到的答案,突然发现少了一个来自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

结果如图所示:

Sql学习第一天——SQL 练习题(建表/sql语句)

--查询发现是正确的。

--分析查看不带条件的左连接

复制代码 代码如下:

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

结果如图所示:

Sql学习第一天——SQL 练习题(建表/sql语句)

--书中给的标准答案是:

复制代码 代码如下:

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

结果如图所示:

Sql学习第一天——SQL 练习题(建表/sql语句)

--书中给的只是多了一个order by 进行定义了排序方式(以numorders这一列的升序进行排序)