SQL Server 2014 查询大全

首先，下载测试数据库


排序（Order by），分页
SELECT 前N个记录时，显示出同样值的最后M-N个（其中第N个记录的值与M-N记录的每个值都相等）。

SELECT TOP 10 WITH TIES *FROM Production.ProductORDER BY ListPrice DESC  

在这个查询中，第10个记录的listPrice和第11,12,13相同，因此会把第11,12,13个记录也显示出来。如果没有加WITH TIES关键字，只会显示前10条。




显示前10%的记录

SELECT TOP 10 PERCENT *FROM Production.ProductORDER BY ListPrice DESC

同样的，可以加上WITH TIES在*前面，列出所有与第10%个记录值相同的记录。



拿第10-20个记录：

-- skip 10 take 10SELECT *FROM Production.ProductORDER BY ProductIDOFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

分组

按降序显示销售员的销售业绩

SELECT SalesPersonID, COUNT(CustomerID) as totalSalesFROM Sales.SalesOrderHeaderwhere salespersonid is not nullGROUP BY SalesPersonIDorder by totalSales desc

5种基本聚合函数：

Count-统计Max-最大值Min-最小值Avg-平均值Sum - 求和

嵌套分组（分组后在每个分组中分组）

SELECT    CustomerID,    SalesPersonID,    COUNT(*) AS NoOfOrdersFROM Sales.SalesOrderHeaderGROUP BY GROUPING SETS(    (CustomerID            ),--销售为NULL时，客户C购买的总数量    (SalesPersonID            ),--客户为NULL时，对应销售S的总数量    (CustomerID, SalesPersonID    ),--销售S卖给客户C的数量    (                ) --总成交数量)ORDER BY SalesPersonID,    CustomerID  

在这个查询中，包含了4种不同的分组：
销售总数
销售员X的成交数量
客户X的购买数量
销售员A卖给客户B的数量


这个查询一种简便等效的写法是使用CUBE关键字。

SELECT    CustomerID,    SalesPersonID,    COUNT(*) AS NoOfOrdersFROM Sales.SalesOrderHeaderGROUP BY CUBE -- CUBE 关键字会根据提供的列，生成所有组合的子集（包括空集）(    (CustomerID, SalesPersonID))ORDER BY SalesPersonID,    CustomerID  

ROLLUP是能够实现类似功能的关键字，例如

...GROUP BY ROLLUP(    (SalesPersonID, CustomerID))...

只会显示出group by为以下三种情况的记录：
(SalesPersonID, CustomerID)--销售员X卖给客户Y的物品数量
(SalesPersonID) -- 销售员X的销售数量
() -- 全部销售量


表连接


基本连接

(INNER) JOINLEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOINCROSS JOIN(DON'T USE)

SELF JOIN.（自连接）
连接比自身BusinessEntityId小10的记录：

SELECT  p1.BusinessEntityID,p1.FirstName +' '+ p1.LastName as fullname, p2.BusinessEntityID as SmallerBusinessIdFROM Person.Person p1 join Person.Person p2on p1.BusinessEntityID = p2.BusinessEntityID + 10

窗口函数OVER语句，排名


任意聚合函数(列) OVER() AS xx。
例子：

SELECT    SalesOrderID,    SalesOrderNumber,    COUNT(*) OVER()            AS NoOfOrders,    COUNT(SalesPersonID) OVER()    AS OrdersWithSalesPerson,    AVG(SubTotal) OVER()        AS AvgSubTotal,    MAX(SubTotal) OVER()        AS MaxSubTotal,    MIN(SubTotal) OVER()        AS MinSubTotal,    SUM(SubTotal) OVER()        AS TotalSubTotalFROM Sales.SalesOrderHeader 

这个查询中的OVER()是OPTIONAL的。


OVER内部使用PARTITION BY，达到内部分组的目的。
例子：

SELECT    SalesOrderID,    SalesOrderNumber,    CustomerID,    SUM(SubTotal) OVER(PARTITION BY CustomerID) AS TotalSubTotalPerCustomer,    SUM(SubTotal) OVER() AS TotalFROM Sales.SalesOrderHeader  

本查询中，除了对所有subtotal进行求和之外，对customer进行分组，对每组再次对subtotal进行求和。
如果在partition之后加上 ORDER BY SALESORDERNUMBER则能够叠加显示出每个customer的SUBTOTAL。
例子：

SELECT    SalesOrderID,    SalesOrderNumber,    CustomerID,SubTotal,    SUM(SubTotal) OVER(PARTITION BY CustomerID ORDER BY SALESORDERNUMBER ) AS SubTotalPerCustomerSoFar,    SUM(SubTotal) OVER() AS TotalFROM Sales.SalesOrderHeader  

另一种等价写法：

SELECT    SalesOrderID,    SalesOrderNumber,    OrderDate,    CustomerID,    SubTotal,    SUM(SubTotal) OVER(PARTITION BY CustomerID                ORDER BY OrderDate                ROWS BETWEEN UNBOUNDED PRECEDING                AND CURRENT ROW) AS TotalSubTotalSoFarForCustomerFROM Sales.SalesOrderHeaderORDER BY CustomerID, OrderDate  

排名
以下查询演示了ROW_NUMBER函数的使用，排名函数RANK()，以及密度排名函数DENSE_RANK()的用法。

SELECT    SalesOrderID,    SalesOrderNumber,    CustomerID,    ROW_NUMBER() OVER(ORDER BY CustomerID) AS RowNumber,    RANK() OVER(ORDER BY CustomerID) AS [Rank],    DENSE_RANK() OVER(ORDER BY CustomerID) AS DenseRankFROM Sales.SalesOrderHeaderORDER BY CustomerID  

其中RANK()与DENSE_RANK()的区别在于，前者关心现在的总位置，后者关心现在的相对位置。





使用LAG与LEAD函数
查询出当前，上一个，下一个CUSTOMER的销售情况：

SELECT    SalesOrderID,    SalesOrderNumber,    LAG(CustomerID) OVER(ORDER BY OrderDate) AS PreviousCustomer,    CustomerID AS CurrentCustomer,    LEAD(CustomerID) OVER(ORDER BY OrderDate) AS NextCustomerFROM Sales.SalesOrderHeaderORDER BY OrderDate