Sql学习第八天——SQL 关于pivot

SQL 关于pivot

解释： PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。

还是通过实例来了解用法和作用：

先建表一（[dbo].[Customers] 字段说明：customerid -- 消费者id ， city -- 所在城市）：

CREATE TABLE [dbo].[Customers](
    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [city] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [customerid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

向表一插入数据：

insert into dbo.Customers values('FISSA','Madrid');
insert into dbo.Customers values('FRNDO','Madrid');
insert into dbo.Customers values('KRLOS','Madrid');
insert into dbo.Customers values('MRPHS','Zion');

查询所插入的数据：

select * from dbo.Customer

结果如图：

再建表二（[dbo].[Orders] 字段说明：orderid -- 订单id ， customerid -- 消费者id）：

CREATE TABLE [dbo].[Orders](
    [orderid] [int] NOT NULL,
    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
    [orderid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

向表二插入数据：

insert into dbo.Orders values(1,'FRNDO');
insert into dbo.Orders values(2,'FRNDO');
insert into dbo.Orders values(3,'KRLOS');
insert into dbo.Orders values(4,'KRLOS');
insert into dbo.Orders values(5,'KRLOS');
insert into dbo.Orders values(6,'MRPHS');
insert into dbo.Orders values(7,null);

查询插入的数据：

select * from dbo.orders

结果如图：

操作：

在了解PIVOT之前先看这一个题目：

题目1：每个所在城市的消费者的订单情况（请用“没有订单”，“小于两个订单”，“超过两个订单”）Sql语句如下：

select c.customerid  as '消费者', city  as '所在城市',  
       case
       when count(orderid) = 0 then '没有订单'
       when count(orderid) <= 2 then '小于两个订单'
       when count(orderid) > 2 then '超过两个订单'
       end as '种类'
from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid
group by c.customerid,city

结果如图：

这个很容易理解不作详解

再引入题目2：每个城市的每种订单情况（请用“没有订单”，“小于两个订单”，“超过两个订单”）的数

方法一：用普通的sql语句进行写：

select 所在城市 , count(case when 种类 = '没有订单' then 消费者 end) as [没有订单],
                  count(case when 种类 = '小于两个订单' then 消费者 end) as [小于两个订单],
                  count(case when 种类 = '超过两个订单' then 消费者 end) as [超过两个订单]
from (
select c.customerid  as '消费者', city  as '所在城市',  
       case
       when count(orderid) = 0 then '没有订单'
       when count(orderid) <= 2 then '小于两个订单'
       when count(orderid) > 2 then '超过两个订单'
       end as '种类'
from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid
group by c.customerid,city
) as d
group by 所在城市

结果如图：

方法二：用pivot看它的神奇之处：

select 所在城市,没有订单,小于两个订单,超过两个订单
from (
select c.customerid  as '消费者', city  as '所在城市',  
       case
       when count(orderid) = 0 then '没有订单'
       when count(orderid) <= 2 then '小于两个订单'
       when count(orderid) > 2 then '超过两个订单'
       end as '种类'
from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid
group by c.customerid,city
) as d
pivot(count(消费者) for 种类 in (没有订单,小于两个订单,超过两个订单)) as p

结果如图：

分析（在看分析时请结合方法一，便于理解）：

在看方法二的sql语句可以发现pivot把d的两列（消费者，种类）作为输入参数，而没有作为输入参数（所在城市 ）的列作了一个隐式的分组（相当于方法一最后的：group by 所在城市 ），其次它又对 in 子句作了如下操作sql操作：

case when 种类 = '没有订单' then 消费者 end,
case when 种类 = '小于两个订单' then 消费者 end,
case when 种类 = '超过两个订单' then 消费者 end

最后对每个case表达式作指定聚合函数sql代码如下：

count(case when 种类 = '没有订单' then 消费者 end) as [没有订单],
count(case when 种类 = '小于两个订单' then 消费者 end) as [小于两个订单],
count(case when 种类 = '超过两个订单' then 消费者 end) as [超过两个订单]

好了通过结合方法一与方法二就应该能够理解pivot的用法了！