用 SQLServer 2005 PIVOT 函数 巧解题

补充:巧不巧,大家看下就知道了,如果觉得不巧,再拍砖也不迟,^_^

背景

SQLServer 2005 联机从书:

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

UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。

来看下题目:
How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?

In database pubs, use three table stores,sales and titles to implement this requestment.

Now I want to get the result as below:

stor_id stor_name                               

------- --------------------

...

7896    Fricative Bookshop

...

 

这是我进公司时SQL培训出的题.其中的pubs为SQLServer的示例数据库.(向SQLServer中添加pubs的代码见附件)

 

Pubs中stores,sales and titles的设计如下,其中types of books信息存在titles库中的type字段中：

 

查看 stores,sales and titles 关联后的表

with temp as
(
  SELECT dbo.stores.stor_id,dbo.stores.stor_name,dbo.titles.type,dbo.sales.qty
  FROM dbo.sales 
    INNER JOIN dbo.stores
      ON dbo.sales.stor_id=dbo.stores.stor_id
    INNER JOIN dbo.titles
      ON dbo.sales.title_id=dbo.titles.title_id
)

//如果对temp进行查询，则结果为:

 

一、常规的思路:

利用子查询

select distinct stor_id,stor_name
from temp as t
where [type]= 'business' and exists
  (
    select * from temp where stor_id = t.stor_id and [type] = 'mod_cook'
  )

 

这种方法容易理解，但是执行效率低，会出现多次扫描表！我们来看第二种思路（我的解法）

 

二、利用PIVOT函数，来个行列转换:

         把type的business与mod_cook列值转换成表的字段，就可以直接用where business is not null and mod_cook is not null 直接筛取了

详细过程：

1．转置

SELECT stor_id,stor_name ,[business],[mod_cook]
FROM temp AS S
PIVOT
(
  SUM(qty)
  FOR [type] in([business],[mod_cook])
) AS R

结果：

 

2．据条件筛：WHERE [business] IS NOT NULL AND [mod_cook] IS NOT NULL

结果：

 

应题目需要，修改后的代码完整如下:

 1SELECT stor_id,stor_name
 2FROM temp AS S
 3PIVOT
 4(
 5  SUM(qty)
 6  FOR [type] in([business],[mod_cook])
 7) AS R
 8WHERE [business] IS NOT NULL AND [mod_cook] IS NOT NULL
 9

 

最终结果:

 

这个种方法比较拗，但利用了PIVOT转置行列避免了多次扫描表，效率不错！

 

也是新手，如果有不对的或是不合理的地方大家多拍砖！

附:向数据库添加Pubs实例库的代码:https://files.cnblogs.com/FojamesChen/pubs.rar