开窗函数_20220103

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

运行结果：