1、深入SQL高级子查询,第三章 上机,作业点评,理论作业 局部变量的声明使用declare关键字声明，赋值使用SET 和 SELECT SET一般将已知的的值赋给变量，SELECT是把查询的结果值赋给变量 BREAK从当前循环中跳出；RETURN结束执行当前代码块，RETURN后面所有的语句都不会再执行；GOTO跳转到标签指定的代码行继续执行。BREAK、RETURN、GOTO都可以从WHILE循环中跳出来 上机作业 select 产品编号,sum(case month(日期) when 1 then 数量 else 0 end) 1月,sum(case month(日期) when 2 the
2、n 数量 else 0 end) 2月,sum(case month(日期) when 3 then 数量 else 0 end) 3月,sum(case month(日期) when 4 then 数量 else 0 end) 4月,sum(case month(日期) when 5 then 数量 else 0 end) 5月,sum(case month(日期) when 6 then 数量 else 0 end) 6月,sum(case month(日期) when 7 then 数量 else 0 end) 7月,sum(case month(日期) when 8 then 数量 e
3、lse 0 end) 8月,sum(case month(日期) when 9 then 数量 else 0 end) 9月,sum(case month(日期) when 10 then 数量 else 0 end) 10月,sum(case month(日期) when 11 then 数量 else 0 end) 11月,sum(case month(日期) when 12 then 数量 else 0 end) 12月 from OrderDetails group by 产品编号,使用Case-End实现的片段代码,上机内容,使用基本子查询 在子查询中使用IN/EXISTS 在子查询
4、中使用ALL/ANY 使用相关子查询,上机目标,实现用户购买游戏卡过程 使用IN、ANY子查询为用户升级角色 使用子查询统计滞销的游戏卡类型和热销的游戏卡类型 使用子查询统计目前还没有任何销量的游戏卡类型 使用NOT IN和相关子查询统计非管理员角色本年度月均购卡消费金额 统计各种游戏卡月平均销售总额,上机效果演示,用户购买游戏卡成功！,用户角色升级成功！,当年每月销售统计,阶段1,训练内容 使用基本子查询 在UPDATE语句中使用子查询 在INSERT语句中使用子查询 使用IN/NOT IN的子查询 需求说明 实现用户“jack123”购卡流程：用户“jack123”想一次购买“NBA篮球卡
5、”5张，要求实现此用户的购卡流程,阶段1,完成时间：50分钟,实现思路 购买游戏卡的过程一共需要6步 查询用户购买的卡类型的卡数量是否足够用户购买 检查用户账户的余额是否足够购买这些 将购买的游戏卡保存到购物历史记录中 将购买的游戏卡信息保存到购物车 更新用户的账户余额 更新游戏卡的状态,阶段1,if(cnt=5) begindeclare balance money,requiremoney moneyselect balance =balance from UserInf where UserName =jack123select requiremoney =5*price from Ca
6、rdType where Id= (select id from CardType where CardTypeName =NBA篮球卡)set requiremoney =requiremoney*(select discount from RoleInf where id= (select UserRoleId from UserInf where UserName =jack123)if(balance=requiremoney)begindeclare temptable table(id int identity(1,1),CardNo nvarchar(20) insert int
7、o temptable SELECT TOP 5 cardno from CardInf where CardStateId=(select id from CardState where State =未售出) and CardTypeId=(select id from cardtype where CardTypeName=NBA篮球卡) declare id int =1while(id=5)begininsert into ShopHistory values(jack123,(select CardNo from temptable where id=id),getdate()se
8、t id+=1endif(ERROR=0)begininsert into ShoppingCart values(jack123,(select id from CardType where CardTypeName =NBA篮球卡),5)update CardInf set CardStateId=2 where CardNo in (select CardNo from temptable)if(ERROR =0)beginupdate UserInf set Balance -=requiremoney where UserName =jack123 endend end end go
9、,定义变量保存要购买的5章卡,阶段1,需求说明 使用子查询统计在所有已销售的游戏卡中滞销的游戏卡类型、热销的游戏卡类型及还没有销售量的游戏卡类型：总销售量小于10张的为滞销的游戏卡类型，总销售量大于1000张的为热销的游戏卡类型，总销售量为0的为没有销售量的游戏卡类型 实现思路 以查询滞销游戏卡为例：使用group by 和having查询出销售量小于10的游戏卡类型ID，然后以此作为WHERE条件，使用IN与这些结果比较，查询出游戏卡的类别名称,参考代码,阶段小结,共性问题集中讲解,如何使用变量保存多行记录 如何使用子查询更新多行记录 如何使用子查询添加数据,阶段2,训练内容 相关子查询 多
10、表联接查询 ANY子查询 需求说明 在用户每次购买游戏卡后系统，都应该依据其消费金额来为用户升级（指角色升级），角色级别越高，用户折扣越高。要求实现用户角色升级功能。例如试着为用户“jack123”升级 用户角色升级的条件为：单日消费达到200元或总消费达到1000元，均可升一级。但最高不能升级成“超级管理员” 要求分别使用相关子查询和联接查询实现,阶段2,完成时间：50分钟,实现思路一 使用联接查询 用户的消费记录保存在购物历史记录表ShopHistory中，用户购买卡的类型不同，价格也就不同，所以需要联接表CardType查询价格 用户每天购卡的金额要统计起来，即必须根据不同类型的卡将每日
11、购买的金额进行分组统计 单张卡的类型ID在表CardInf中，所以必须与表CardInf做联接查询,阶段2,update UserInf set UserRoleId=sharprole where username=jack123 and 200 any(select SUM(ct.Price) as 每日消费from ShopHistory s,CardInf ci,CardType ctwhere s.CardNo=ci.CardNo and ci.CardTypeId=ct.Idand s.UserName=jack123group by CONVERT(char(10),s.Shop
12、Time,120),使用联接查询：单日消费满200元的角色升级,代码片段,阶段2,实现思路二使用相关子查询 先通过相关子查询查询出用户jack123所购买的所有卡的卡号和金额 然后把这个结果集按日期进行分组排序,阶段2,select sum(re.sumPay) sum_pay from (select s.UserName, s.CardNo, s.ShopTime, (select SUM(price) from CardInf as ci,CardType as ct where s.CardNo=ci.CardNo and ci.CardTypeId=ct.Id ) as sumPay from ShopHistory as s where s.UserName=jack123) as re,使用相关子查询，单日消费满200元的角色升级,代码片段,阶段2,参考答案,需求说明 使用NOT IN子查询统计非管理员角色的本年度月均购卡消费金额 实现思路 通过子查询获得所有用户的购卡信息（包括卡号、金额、时间、用户名 分组计算每年度各月的平均消费金额，在分组前还需要使用not in 过滤掉管理员的销售金额,阶段小结,共性问题集中讲解,子查询结果集作为虚表参与外部查询 相关子查询的执行方式 ANY的使用方法,总结,总结并布置作业,
