笔记二 sql 基础知识（子查询）

一. 透视

列转行，用case when 即可，也可用其提供的函数PIVOT

ps： 逆透视，行转列，比较少用，不掌握

二 ，更新数据

UPDATE BL SET BL.StockID = IDS.stockID,BL.StockPlaceID = IDS.stockPlaceID ,IsInStorage = 1
FROM W_SC$$BarcodeLabel BL
INNER JOIN t_CombinationIDS IDS ON BL.uid = IDS.barcodeID

避免插入乱码

insert into Sys_User values(2,N'蝈蝈',N'唐宁街十号',N'奥巴马的上铺','2017-11-16 20:45:05.603')

 

三 ，删除数据

TRUNCATE --清楚记录，id 不会增加

delete --不清除记录，id 会增加

 

四  联接查询

1.内连接：先笛卡尔积，然后根据指定的谓词对结果进行过滤

select e.empid,e.firstname,e.lastname,o.orderid
from hr.Employees as e
    join sales.Orders as o
    on e.empid=o.empid;

 

2.外连接： left outer join和left join的区别 --后者是前者的简写，

笛卡尔积 （m*n）→对结果过滤→添加外部行 

通过例子来理解外联结：根据客户的客户ID和订单的客户ID来对Customers表和Orders表进行联接，并返回客户和他们的订单信息。该查询语句使用的联接类型是左外连接，所以查询结果也包括那些没有发出任何订单的客户；

--LEFT OUTER JOIN
select c.custid,c.companyname,o.orderid
from sales.Customers as c
  left outer join sales.Orders as o
  on c.custid=o.custid;

　　另外，需要注意的是在对外联结中非保留值得列值进行过滤时，不要再WHERE子句中指定错误的查询条件。

　　例如，下面请求返回在2007年2月12日下过订单的客户，以及他们的订单。同时也返回在2007年2月12日没有下过订单的客户。这是一个典型的左外连接的案例，但是我们经常会犯这样的错误：

select c.custid,c.companyname,o.orderid,o.orderdate
from sales.Customers as c
    left outer join sales.Orders as o
    on c.custid=o.custid 
where o.orderdate='20070212';

 

　　执行结果如下：

      

　　这是因为对于所有的外部行，因为它们在o.orderdate列上的取值都为NULL，所以WHERE子句中条件o.orderdate='20070212'的计算结果为UNKNOWN，因此WHERE子句会过滤掉所有的外部行。

　　我们应该将这个条件搬到on后边：

select c.custid,c.companyname,o.orderid,o.orderdate
from sales.Customers as c
    left outer join sales.Orders as o
    on c.custid=o.custid 
        and o.orderdate='20070212';

 

　　这下的执行结果如下：

      

 

五，子查询。！！

（1）做为条件语句的子查询

select orderid
from sales.Orders
where empid in (select e.empid 
    from hr.Employees as e
    where e.lastname like N'D%');

（2）做为计算字段的子查询

　　① 如何表示前一个或后一个记录？逻辑等式：上一个->小于当前值的最大值；下一个->大于当前值的最小值；

-- 上一个订单ID
select orderid, orderdate, empid, custid,
(
select MAX(o2.orderid) 
from sales.Orders as o2
where o2.orderid<o1.orderid
) as prevorderid 
from sales.Orders as o1;

 

　　② 如何实现连续聚合函数？在子查询中连续计算

-- 连续聚合
select orderyear, qty, 
(select SUM(o2.qty) 
 from sales.OrderTotalsByYear as o2
 where o2.orderyear<=o1.orderyear) as runqty 
from sales.OrderTotalsByYear as o1
order by orderyear;

 

　　 执行结果如下图所示：

        

 PS:通常情况下自连接比子查询要快，但是对于不同的语句，我们需要进行尝试一下