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