
题目:来自Madrid且订单数少于3的消费者
T-SQL是需要优化的。而优化的前提是你对它的执行步骤有清楚的认识。我经常去给讲课或者咨询辅导的时候,就发现很多朋友对此了解甚少。下面截取了我的一个PPT,给大家参考一下
这2张表可以很清晰的看出外链接
代码如下复制代码 SELECT pub_name FROM publishers WHERE NOT EXISTS
(SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =
‘business’)
建表:复制代码 代码如下: 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
–因为没有WITH Rollup和WITH
Cube语句,所以跳过第六步,进入HAVING子句的处理
–所以在含有NULL值的列的时候,就要小心了,not exists与not
in在逻辑上是不等价的
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这一列的升序进行排序)
–第二步:处理ON子句,只把那些两个表的customerid匹配的行找出来,我把它们放到VT2中去
SELECT temp.* INTO #VT2 FROM (SELECT * FROM #VT1 WHERE
Customer=customerid) temp
SELECT * FROM #VT2
–返回6行数据
代码如下复制代码 SELECT pub_name FROM publishers WHERE pub_id NOT IN
(SELECT pub_id FROM titles WHERE type = ‘business’)
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL PRIMARY KEY,
customerid CHAR(5) NULL REFERENCES Customers(customerid)
);
下面的查询查找已经不销售的书的名称:
/*
准备数据架构和数据
*/
SET NOCOUNT ON;
USE tempdb;
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
);
(7 行受影响)
–我们再用IN方法
–第七步:使用SELECT 的字段列表过滤结果集
SELECT #VT6.Customer,#VT6.orderidcount as numorders INTO #VT7 FROM
#VT6
SELECT * FROM #VT7
–还是2行数据,只不过只有两个列了
customerid city orderid
customerid
接着看
INSERT INTO dbo.Customers(customerid, city) VALUES(‘FISSA’, ‘Madrid’);
INSERT INTO dbo.Customers(customerid, city) VALUES(‘FRNDO’, ‘Madrid’);
INSERT INTO dbo.Customers(customerid, city) VALUES(‘KRLOS’, ‘Madrid’);
INSERT INTO dbo.Customers(customerid, city) VALUES(‘MRPHS’, ‘Zion’);
set identity_insert Orders off;
/*第一步:处理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)
customerid city orderid
customerid
代码如下复制代码 select custid,companynamefrom Customers as Cwhere
country=N’中国’and exists (select * from Orders as O where
O.custid=C.custid);–返回–custid companyname–1 大众–3 奔驰
–第五步:处理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行数据,根据客户分组,统计了订单的个数
–这里会不会去统计其他列的汇总呢
CREATE TABLE dbo.Customers
(
customerid CHAR(5) NOT NULL PRIMARY KEY,
city VARCHAR(10) NOT NULL
);
create table t1 (c1 number,c2 number);create table t2 (c1 number,c2
number);
–第三步:根据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
(8 行受影响)
两者的区别: EXISTS:后面可以是整句的查询语句如:SELECT * FROM titles
IN:后面只能是对单列:SELECT pub_id FROM titles NOT EXISTS:
例如,要查找不出版商业书籍的出版商的名称:
–第八步:跳过DISTINCT,进行OrderBy操作
SELECT #VT7.* INTO #VT8 FROM #VT7 Order By #VT7.Customer
SELECT * FROM #VT8
–返回2行数据,经过排序
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS
NULL NULL 7
NULL
–下面的知识点我们需要认识到:–当列表中有NULL时,in实际会产生一个UNKNOWN的结果,例如
a in(d,b,null)的结果是UNKNOWN,而a not in (d,b,null)返回的是not
unknowd仍然是unknowd–而not in与not exists则结果会很不同,例如a
in(a,b,null)返回的是TRUE,而a not in(a,b,null)返回的肯定是not
true即为false–有了上面的认识,好继续开工了….–我们现在向Orders表插入一行数据
–第四步:处理WHERE
子句,对VT3的结果集进行过滤,我们的条件是city=Madid
SELECT temp.* INTO #VT4 FROM
(SELECT * FROM #VT3 WHERE city=’Madrid’) temp
SELECT * FROM #VT4
–返回6行数据,因为有一个客户不是这个城市的
表如下
上面涉及的3个表的记录数都不小,均在百万左右。根据这种情况,我想到了前不久看的tom的一篇文章,说的是exists和in的区别,in
是把外表和那表作hash
join,而exists是对外表作loop,每次loop再对那表进行查询。这样的话,in适合内外表都很大的情况,exists适合外表结果集很小的情况。
下面这个脚本可以解释这个过程
FRNDO Madrid 1
FRNDO
FRNDO Madrid 2
FRNDO
KRLOS Madrid 3
KRLOS
KRLOS Madrid 4
KRLOS
KRLOS Madrid 5
KRLOS
MRPHS Zion 6
MRPHS
代码如下复制代码 select custid,companyname from Customers as Cwhere
country=N’美国’and custid not in(select custid from Orders);
其结果如下
然而,如果使用 IN,少键入一些字符也可以得到同样的结果:
–这个查询结束演示。完整的8个步骤
INSERT INTO dbo.Orders(orderid, customerid) VALUES(1, ‘FRNDO’);
INSERT INTO dbo.Orders(orderid, customerid) VALUES(2, ‘FRNDO’);
INSERT INTO dbo.Orders(orderid, customerid) VALUES(3, ‘KRLOS’);
INSERT INTO dbo.Orders(orderid, customerid) VALUES(4, ‘KRLOS’);
INSERT INTO dbo.Orders(orderid, customerid) VALUES(5, ‘KRLOS’);
INSERT INTO dbo.Orders(orderid, customerid) VALUES(6, ‘MRPHS’);
INSERT INTO dbo.Orders(orderid, customerid) VALUES(7, NULL);
INSERT INTO Customers(custid, companyname,country) VALUES(1, N’大众’,
N’中国’); INSERT INTO Customers(custid, companyname,country) VALUES(2,
N’宝马’, N’美国’); INSERT INTO Customers(custid, companyname,country)
VALUES(3, N’奔驰’, N’中国’); INSERT INTO Customers(custid,
companyname,country) VALUES(4, N’奇瑞’, N’德国’); INSERT INTO
Customers(custid, companyname,country) VALUES(5, N’福特’, N’美国’); set
identity_insert Customers off; set identity_insert Orders
on;–custid代表员工号 INSERT INTO Orders(orderid, custid) VALUES(1,1);
INSERT INTO Orders(orderid, custid) VALUES(2,2); INSERT INTO
Orders(orderid, custid) VALUES(3,3); INSERT INTO Orders(orderid, custid)
VALUES(4,4); INSERT INTO Orders(orderid, custid)
VALUES(5,5);–查看表的数据select custid,companyname,country from
Customers;select orderid,custid from Orders;–插入数据成功
/*
一个测试查询,检索那些订单个数小于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;
–查询来自中国,而且下过订单的所有客户
相关文章
- 德姆oUser.然后创建客商并mapping到数据库,那么要怎么准确的去除这个Windows认证账号呢
- 尚无检索出一条记下
- 本条还以为不错的八个积存进度
- //创设多少个数据库 ,数据库相关
- 澳门新葡8455最新网站Centos7使用yum安装MySQL甚至哪些落实长途连接,Linux平台上引入应用RPM包来安装Mysql
- start 是二个莫西干发型,sql截取黄金时代段字符串并对该字符串举行沟通的形式
- 澳门新葡8455最新网站此刻在此外二个Session试行重新建立Procedure的操作,假如是合营到作者鲜明的20170901的表就记录下来
- 直白都以只把TCP/IP设为Enabled
- 澳门新葡8455最新网站:直白改数据库的肯定不仅仅本身三个,当你使用 EF Code First
- 存储进度中也是有相应的原则剖断,MySql存款和储蓄进程—3、变量