IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
  • 登录 注册

    [原]SQL Server 2014 查询大全

    csharp25发表于 2017-02-05 16:13:19
    love 0
    首先,下载测试数据库


    排序(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



沪ICP备19023445号-2号
友情链接