Sql advabce

1.窗口函数

• 不使用分组且可以展示出所有表的信息

#查询每个学生的总成绩
SELECT TOP (1000) [s_id]
      ,[c_id]
      ,[s_score]
      ,totalScore = SUM(s_score) over(PARTITION BY [s_id])
  FROM [SQL50].[dbo].[score]

image.png

• PARTITION BY s_id 的意思是 根据学号分组

SELECT  sc.[s_id]
        ,st.s_name
      ,[c].c_name
      ,sc.s_score
     ,total_score = sum(sc.s_score) over(PARTITION BY (sc.s_id))
     --,ranking = ROW_NUMBER() over(PARTITION BY sc.s_id order by sc.s_score desc)
     ,rankingWithRank = rank() over(PARTITION BY sc.s_id order by sc.s_score desc)
     ,rankingWithDens = DENSE_RANK() over(PARTITION BY sc.s_id order by sc.s_score desc)
  FROM [SQL50].[dbo].[score] as sc left join student st on st.s_id= sc.s_id
  left join course as c on sc.c_id =c.c_id 

2.相关子查询（correlated subquery）

在[SalesOrderHeader]表里，查询出每个商品销量大于1的产品信息。销量在SalesOrderDetail表里**

image.png

• 我的方法
  思维过程，先在SalesOrderDetail里分组查询出，每个订单数量大于1的总数有多少，
  然后通过内连接，将SalesOrderHeader与该表内链接

#写法1
  SELECT a.[SalesOrderID]
      ,[OrderDate]
      ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
      ,b.sum_qty
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderHeader] a  join (
   SELECT SalesOrderID,COUNT(SalesOrderID) sum_qty
  FROM [AdventureWorks2008R2].[Sales].SalesOrderDetail where OrderQty>1 group by SalesOrderID) b on 
    a.[SalesOrderID] =b.SalesOrderID

#写法2
  SELECT a.SalesOrderID, COUNT(a.SalesOrderID) AS sum_qty, 
       MAX(b.OrderDate) AS OrderDate, SUM(b.SubTotal) AS SubTotal, 
       SUM(b.TaxAmt) AS TaxAmt, SUM(b.Freight) AS Freight, SUM(b.TotalDue) AS TotalDue
FROM [AdventureWorks2008R2].[Sales].SalesOrderDetail a 
INNER JOIN [AdventureWorks2008R2].[Sales].[SalesOrderHeader] b 
ON a.SalesOrderID = b.SalesOrderID
WHERE a.OrderQty > 1 
GROUP BY a.SalesOrderID;

#写法3：
SELECT a.SalesOrderID, COUNT(a.SalesOrderID) AS sum_qty, b.OrderDate, b.SubTotal, b.TaxAmt, b.Freight, b.TotalDue
FROM [AdventureWorks2008R2].[Sales].SalesOrderDetail a 
INNER JOIN [AdventureWorks2008R2].[Sales].[SalesOrderHeader] b 
ON a.[SalesOrderID] = b.SalesOrderID
WHERE a.OrderQty > 1 
GROUP BY a.SalesOrderID, b.OrderDate, b.SubTotal, b.TaxAmt, b.Freight, b.TotalDue;

注意：如果你在SELECT语句中使用了聚合函数（例如COUNT），那么你的GROUP BY子句应该包括所有未被聚合的列。在你的查询中，你选择了b.OrderDate、b.SubTotal、b.TaxAmt、b.Freight和b.TotalDue，但这些列在GROUP BY子句中没有出现，这可能会导致一些数据库管理系统报错。

• 新的方法(相关子查询)
• 这种方法类似于for循环，每一条SalesOrderID的查询，他都会执行一次sum_qty的子查询，

SELECT TOP (1000) [SalesOrderID]
      ,[OrderDate]
      ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
      ,sum_qty = (
         SELECT COUNT(1)  FROM [AdventureWorks2008R2].[Sales].SalesOrderDetail b
         where a.SalesOrderID = b.SalesOrderID and b.OrderQty>1
      )
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderHeader] a

查看至少有一单超过10000美元的订单号**

  SELECT a.[SalesOrderID]
      ,a.[OrderDate]
      ,a.[TotalDue]
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderHeader] a
  where EXISTS (
    select 1 from [AdventureWorks2008R2].[Sales].SalesOrderDetail b
    where b.LineTotal>10000
    and a.[SalesOrderID] = b.SalesOrderID
  )