2022年,这两天再将窗口函数、CTE、Offset-Fetch、表的物理存储方式再看一遍
SELECT C.CustID, COUNT(O.OrderID) AS OrderNum FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.CustID = O.CustID WHERE C.City = 'Beijing' GROUP BY C.CustID HAVING COUNT(O.OrderID) < 3 ORDER BY OrderNum; Select * From Sales.Customer as C Select * From Sales.SalesOrderHeader as D Select * From Sales.SalesOrderHeaderSalesReason as E Select * From Sales.Customer as C Left outer join Sales.SalesOrderHeader as O ON C.CustomerID = O.CustomerID WHERE C.TerritoryID = '5' GROUP BY C.CustomerID HAVING COUNT(O.SalesOrderID IF OBJECT_ID('dbo.Students','U') IS NOT NULL DROP TABLE dbo.Students; CREATE TABLE dbo.Students ( ClassID int, StudentName nvarchar(20), Achievement numeric(5,2) ); INSERT INTO dbo.Students VALUES (1,'Grace',99.00), (1,'Andrew',99.00), (1,'Janet',75.00), (1,'Margaret',89.00), (2,'Steven',86.00), (2,'Michael',72.00), (2,'Robert',91.00), (3,'Laura',75.00), (3,'Ann',94.00), (3,'Ina',80.00), (3,'Ken',92.00); SELECT * FROM dbo.Students
运行结果:
OVER 子句指定了窗口规范的三个主要部分:分区、排序和框架
Select ClassID,StudentName,Achievement, AVG(Achievement) over(partition by ClassID order by Achievement desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunAVGAch From dbo.Students
运行结果: