SOMEONE‘S WORLD

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

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

Pubs示例库设计信息,没用过Pubs示例库的可以参考下
Pubs中stores,sales and titles的设计如下,其中types of books信息存在titles库中的type字段中：

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

1with temp as
2(
3  SELECT dbo.stores.stor_id,dbo.stores.stor_name,dbo.titles.type,dbo.sales.qty
4  FROM dbo.sales 
5    INNER JOIN dbo.stores
6      ON dbo.sales.stor_id=dbo.stores.stor_id
7    INNER JOIN dbo.titles
8      ON dbo.sales.title_id=dbo.titles.title_id
9)

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

一、常规的思路:

利用子查询

1select distinct stor_id,stor_name
2from temp as t
3where [type]= 'business' and exists
4  (
5    select * from temp where stor_id = t.stor_id and [type] = 'mod_cook'
6  )

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

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

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

详细过程：

1．转置

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

结果：

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
10

最终结果:

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

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

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