宝马娱乐在线112222 > 网络应用 > Sql学习第一天——SQL 练习题(建表/sql语句)宝马娱

原标题:Sql学习第一天——SQL 练习题(建表/sql语句)宝马娱

浏览次数:194 时间:2020-03-13

建表:复制代码 代码如下: set nocount on --当 SET NOCOUNT 为 ON 时,不返回计数。当 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

--这个查询结束演示。完整的8个步骤

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这一列的升序进行排序)

/*第一步:处理FROM子句,把来源的表进行CROSS JOIN(笛卡尔乘积)
我这里把结果做一个生成表查询,写到一个临时表(VT1)中去
*/
SELECT C.customerid as Customer,c.city,o.*  INTO #VT1 FROM dbo.Customers C,dbo.Orders  O
SELECT * FROM #VT1
--返回28行数据(4*7)

题目:来自Madrid且订单数少于3的消费者

下面这个脚本可以解释这个过程

--因为没有WITH Rollup和WITH Cube语句,所以跳过第六步,进入HAVING子句的处理

--第七步:使用SELECT 的字段列表过滤结果集
SELECT #VT6.Customer,#VT6.orderidcount as  numorders  INTO #VT7 FROM #VT6
SELECT * FROM #VT7
--还是2行数据,只不过只有两个列了

/*
一个测试查询,检索那些订单个数小于3的客户,并且按订单总数排序(升序)
*/
SELECT C.customerid, COUNT(O.orderid) AS numorders
FROM dbo.Customers AS C
  LEFT OUTER 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;

宝马娱乐在线112222 1

--第三步:根据JOIN语句的类型,决定是否要添加行到VT2中去,例如如果是LEFT  JOIN的话,那么就要检查坐边的表(我们这里是customers表)的连接键值是否都存在,如果不存在就要去添加到VT2中
SELECT temp.* INTO #VT3 FROM
(SELECT * FROM #VT2
UNION ALL
SELECT CustomerID,City,NULL,NULL FROM dbo.Customers c WHERE NOT EXISTS(SELECT DISTINCT Customer FROM #VT2 WHERE Customer=c.CustomerID)) temp
SELECT * FROM #VT3
--返回7行数据,其中有一个客户,因为没有订单,这一步中被添加进来。它的Orders的记录被标记为NULL

/*
这个脚本用来帮助用户理解T-SQL查询的逻辑顺序和原理。
作者:陈希章
*/

--第五步:处理GROUP子句,进行分类汇总
SELECT temp.* INTO #VT5 FROM
(SELECT Customer,COUNT(OrderID)  as orderidcount,COUNT(city) as citycount,Count(customerid)  as customeridcount FROM #VT4 GROUP BY Customer) temp
SELECT * FROM #VT5
--返回3行数据,根据客户分组,统计了订单的个数
--这里会不会去统计其他列的汇总呢

本文由宝马娱乐在线112222发布于网络应用,转载请注明出处:Sql学习第一天——SQL 练习题(建表/sql语句)宝马娱

关键词:

上一篇:Sql学习第一天——SQL UNION 和 UNION ALL 操作符认识

下一篇:没有了