SQL Server 2014 查询大全

首先，下载测试数据库


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

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

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




显示前10%的记录

SELECT TOP 10 PERCENT *
FROM Production.Product
ORDER BY ListPrice DESC

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



拿第10-20个记录：

-- skip 10 take 10
SELECT *
FROM Production.Product
ORDER BY ProductID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

分组

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

SELECT SalesPersonID, COUNT(CustomerID) as totalSales
FROM Sales.SalesOrderHeader
where salespersonid is not null
GROUP BY SalesPersonID
order by totalSales desc

5种基本 聚合函数：

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

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

SELECT
    CustomerID,
    SalesPersonID,
    COUNT(*) AS NoOfOrders
FROM Sales.SalesOrderHeader


GROUP 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 NoOfOrders
FROM Sales.SalesOrderHeader


GROUP BY CUBE -- CUBE 关键字会根据提供的列，生成所有组合的子集（包括空集）
(
    (CustomerID, SalesPersonID)
)
ORDER BY SalesPersonID,
    CustomerID  

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

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

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


表连接


基本连接

(INNER) JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN(DON'T USE)

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

SELECT  p1.BusinessEntityID,
		p1.FirstName +' '+ p1.LastName as fullname, 
		p2.BusinessEntityID as SmallerBusinessId
		FROM Person.Person p1 
		join Person.Person p2
on 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 TotalSubTotal
FROM Sales.SalesOrderHeader 

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


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

SELECT
    SalesOrderID,
    SalesOrderNumber,
    CustomerID,
    SUM(SubTotal) OVER(PARTITION BY CustomerID) AS TotalSubTotalPerCustomer,
    SUM(SubTotal) OVER() AS Total
FROM 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 Total
FROM 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 TotalSubTotalSoFarForCustomer
FROM Sales.SalesOrderHeader
ORDER 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 DenseRank
FROM Sales.SalesOrderHeader
ORDER 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 NextCustomer
FROM Sales.SalesOrderHeader
ORDER BY OrderDate