SQL：存储过程中创建动态视图

需求：新建一存储过程 proc5，实现查询
购买过指定书目ID的用户
同时
该用户购买过的购买次数最多的前3名书目名称及购买次数

解决过程：

1. 创建存储过程，为指定ID创建变量

CREATE PROCEDURE proc5 @book_id int

2. 通过@book_id查询购买过指定书目ID的用户userId

select distinct(userID) 
from orderBook ,orderInfo 
where orderBook.orderID=orderInfo.orderID 
      and bookID=@book_id

3. 通过步骤2的子查询,就可以查询这些用户买的书籍以及购买的次数

select userID ,bookID ,count(orderbook.orderID) as buy_times
from orderBook ,orderInfo 
where orderBook.orderID=orderInfo.orderID 
  and userID in (select  distinct(userID) 
                 from orderBook ,orderInfo 
			     where orderBook.orderID=orderInfo.orderID 
					   and bookID=@book_id
group by userID,bookID 
order by buy_times desc

此时的查询结果很长，查询内容忽略
因为还要进一步的筛选：这些用户购买的书籍次数前三

此时的结果只有三个字段，而执行的sql语句却很长
解决办法：
通过创建临时视图，即使用完就删除，帮助进一步的筛选查询
注意：创建排序视图使用order by 是不正确的，因为视图只是对查询结果保存的一张表

CREATE VIEW v_result AS
---------------------------------------------
select userID ,bookID ,count(orderbook.orderID) as buy_times
from orderBook ,orderInfo 
where orderBook.orderID=orderInfo.orderID 
  and userID in (select  distinct(userID) 
                 from orderBook ,orderInfo 
			     where orderBook.orderID=orderInfo.orderID 
					   and bookID=@book_id
group by userID,bookID 

此时只需要考虑userId，bookId，buy_times这三个字段

select userId，bookId，buy_times 
from v_result
order by buy_times desc 

4. 回到解题过程，此时需求是: 返回这些用户购买的书籍次数前三的书籍bookId
5. 意思时返回：分组后返回同组数据的前三
   以下是参考：
   sql查询各科成绩前三名----详述过程，思路清晰不烧脑
   COUN函数的区别

select *
from v_result as v1
where 3>(select count(1) 
         from v_result as v2 
         where v1.userID=v2.userID 
             and v1.buy_times>v2.buy_times)
order by userID desc ,bookID desc

6. 将结果写入存储过程，完成！

CREATE PROCEDURE proc5 @book_id int 
AS
---------------------------------------------
declare @sql_str varchar(max)=''
select @sql_str =
'CREATE VIEW v_result AS 
select userID ,bookID ,count(orderbook.orderID) as buy_times
from orderBook ,orderInfo 
where orderBook.orderID=orderInfo.orderID 
     and userID in (select  distinct(userID) 
                     from orderBook ,orderInfo 
                     where orderBook.orderID=orderInfo.orderID 
                      and bookID='+CONVERT(varchar,@book_id)+')
group by userID,bookID '
-------------------------------------------------------
--print(@sql_str)测试
--视图作为临时表需要使用完之后删除
if exists (select * from sys.objects where name = 'v_result')
      drop view v_result
exec(@sql_str)
----------------------------------------------------------
select *
from v_result as v1
where 3>(select count(1) 
         from v_result as v2 
         where v1.userID=v2.userID 
             and v1.buy_times>v2.buy_times)
order by userID desc ,bookID desc

问题一：在存储过程中使用CREATE VIEW会报错：
参考：
存储过程中创建动态视图
问题二：在创建动态视图时候一开始声明varchar（255）通过print（）打印出来不能完整显示要exec（）执行的字符串：
参考：
varchar长度问题