导读:1建站知识来自Madrid且订单数少于3的消费者,针对这个要求作出以下:建表做题分析以及sql语句的写法，感兴趣的朋友可以参考下seo网站优化营销型网站建设。

题目:来自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 value高端网站建设s(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);

---百度seo网站优化---------------------------------------------------------------------------------------------------------------------------

做题分析：

复制代码 代码如下:

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表中没有出现,所以上面的写法会少一个.--推网站seo优化课程翻了上面的答案,又想到了用表的连接,而用内连接出现的情况会和上面的一样,所以我选择了左连接,如下:

复制代码 代码如下:

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

关键词标签: SQL 语句 练习题