PS:这是是学习微软官方教程 《技术内幕 T-SQL查询》的笔记,样例数据库可以到这里下载。因为是自己作总计笔记之用,查资料的学习的同学注意,很可能对你没有帮助。
以下全文引用的是同一个数据库,客户订单场景,创建代码如下:
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders;
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
);
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL PRIMARY KEY,
customerid CHAR(5) NULL REFERENCES Customers(customerid)
);
GO
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');
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);
SELECT * FROM dbo.Customers;
SELECT * FROM dbo.Orders;
以下直观的看一下数据:
我仅补充我之前不会的知识,学习的童鞋请绕道。
1:什么叫添加外部行
on筛选器过后,把保留表的数据行添加回来,非保留表用null填充,所谓保留表就是left outer join,那么左边的表就是保留表。跟据上一篇,添加外部行这一步骤,在on筛选器后where之前发生,亲,明白点什么了没?
2:having到底用到哪里
干四年码奴,竟然没用到过having,也许是真的对having用处不太了解。having只能和group by联用。having是分好组之后的数据进行筛选,所以必须使用聚合函数,注意如果是count那么不能count(*),只能count(key).做两个实验,暂时只能想到这么多。
场景一:订单数不是一条的客户
USE tempdb
SELECT customerid
FROM Orders
GROUP BY customerid
HAVING COUNT(orderid) <> 1
场景二:最新订单ID是6的客户
USE tempdb
SELECT customerid
FROM Orders
GROUP BY customerid
HAVING MAX(orderid) = 6