老铁们集合了今天继续TASK06。SQL训练营的内容我们已经全部学完了TASK06主要是练习题帮大家掌握知识点。使用的数据都是真实数据。更贴近我们的实际工作情况。今天是第四部分错过第三部分的老铁不用着急点击下方链接即可回顾课程。第三部分6.2寻找答案书接上回我们继续寻找答案。上面截图为7月份满减总金额。题目要求是找出总金额最多的商家。所以我们选取第一名即可。根据之前的语句我们还需要加上限制条件。如下selectMerchant_id,Discount_rate,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%andDate_received2016-07-01andDate_received2016-08-01groupbyMerchant_id,Discount_rateorderbysum(cast(substring_index(Discount_rate,:,-1)asunsigned))desclimit1;最后一个语句“limit 1就是限制条件意思是只选取最上面的一行数据。结果如下其实到这里我们第一部分已经完成了即发放优惠券总金额最多的商家已经找出。我们看第二部分发放优惠券张数最多的商家。根据题目一步一步来先求出商家发放优惠券的数量。求数量一般用到函数“count”这个跟我们excel用到的函数是一样的。函数后面加列名count这种聚合函数我们后面要跟“group by函数用来分组即计算每一个组的数量。我们来看语句selectMerchant_id,count(Discount_rate)fromccf_offline_stage1_test_revisedgroupbyMerchant_id;我们看到我在group by后面写了商家ID意味着我要求出每一位商家发放优惠券的数量。也就是按照商家ID分组求出组内的“Discount_rate”数量。我们看答案我们看到优惠券的数量为无序排列我们找出数量最多的就需要按照降序排列即第一行为数量最多的商家。语句如下selectMerchant_id,count(Discount_rate)fromccf_offline_stage1_test_revisedgroupbyMerchant_idorderbycount(Discount_rate)desc;结果如下题目要求是7月份的我们还是加上日期限制语句同part 3语句如下selectMerchant_id,count(Discount_rate)fromccf_offline_stage1_test_revisedwhereDate_received2016-07-01andDate_received2016-08-01andDiscount_rateisnotnullgroupbyMerchant_idorderbycount(Discount_rate)desc;根据我们part3学习到的内容我们再加上limit函数取第一名整个语句就写完了。selectMerchant_id,count(Discount_rate)fromccf_offline_stage1_test_revisedwhereDate_received2016-07-01andDate_received2016-08-01andDiscount_rateisnotnullgroupbyMerchant_idorderbycount(Discount_rate)desclimit1老铁们可以自行敲代码并把答案写在评论区。目前为止我们题目算是完成了。我们来汇总一下代码求优惠券总金额最多和优惠券张数最多的商家。语句分别如下selectMerchant_id,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%andDate_received2016-07-01andDate_received2016-08-01groupbyMerchant_id,Discount_rateorderbysum(cast(substring_index(Discount_rate,:,-1)asunsigned))desclimit1;selectMerchant_id,count(Discount_rate)fromccf_offline_stage1_test_revisedwhereDate_received2016-07-01andDate_received2016-08-01andDiscount_rateisnotnullgroupbyMerchant_idorderbycount(Discount_rate)desclimit1;我们可以用“union all”把两组语句结合如下(selectMerchant_id,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%andDate_received2016-07-01andDate_received2016-08-01groupbyMerchant_id,Discount_rateorderbysum(cast(substring_index(Discount_rate,:,-1)asunsigned))desclimit1)unionall(selectMerchant_id,count(Discount_rate)fromccf_offline_stage1_test_revisedwhereDate_received2016-07-01andDate_received2016-08-01andDiscount_rateisnotnullgroupbyMerchant_idorderbycount(Discount_rate)desclimit1);结果如下我们看到最终的表格是两列即用union all函数表格的行数增加但是列名只能取其中的一个。一般取union all前面语句的列名。有的老铁可能会想到既然可以行的数量增加那可不可列的数量增加呢当然可以。但是这里我们要注意比如有两张表行数相同都是两行。第一张表有三列第二张表有两列。我要合并这两张表即新表有五列两行,实例如下a.1.1 a.1.2 a.1.3 b.1.1 b.1.2a.2.1 a.2.2 a.2.3 b.2.1 b.2.2这个时候我们要问新表的第一行一共五列其中有a表的三列b表的两列。但是这个第一行a表的三列是a表第一行的三列还是第二行的三列呢也就是说a表的第一行后面接的是b表的第几行组合方式如下这就需要连接条件也就是a的第一行的某一个字段与b的第一行的某一个字段相同那么a的第一行后面就是b的第一行。我们知道一般表格的第一列都是ID我们一般可以用id连接。即a.idb.id这样ID相同的行就会合并成一行。我们看我们用union all连接的两个表格没有ID那怎么办那我们就增加ID。如何增加ID大家想想ID是什么ID就是序号。我们学习过窗口函数吧其中一个功能就是增加序号。增加序号有三种方法分别是row_number(),rank(),dense_rank().序号其实就是连续的序列从1开始2345… …。那我们就可以用“row_number()”给每一行增加序号。我们先看语句我们还是先按照发放优惠券总金额排列。selectMerchant_id,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))amountfromccf_offline_stage1_test_revisedwheredate_received2016-08-01andDate_received2016-07-01andDiscount_ratelike%:%groupbyMerchant_idorderbyamountdesc;结果如下我们仔细看一下上面两张截图。一头一尾这个查询结果是没有序列号的。最左面那一列是mysql自带的不算是表格内容。其实我们可以看一下原始表格就是没有序列号的。那我们就在已经生成的这个查询表格中增加一列。我们命名之前生成的表格为amount_rank.语句如下with amount as(select Merchant_id,sum( cast( substring_index(Discount_rate,‘:’,-1) as unsigned)) amountfrom ccf_offline_stage1_test_revisedwhere date_received‘2016-08-01’ and Date_received‘2016-07-01’and Discount_rate like ‘%:%’group by Merchant_id order by amount desc )注意这里的语句,with开头后面接表名as后面为选择语句整段语句加括号。接下来我们要做的就是给amount_rank这张表加序号。我们说过直接用窗口函数就可以。我们就以amount表为基表进行操作。select row_number() over (order by amount) – 添加序号,Merchant_id,amount – 查找商家ID和总金额总金额amount为amount_rank的列from amount; – 从amount中选取with 开头的mysql语句和select开头的语句我们可以看作一个整体。完整的语句如下withamountas(selectMerchant_id,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))amountfromccf_offline_stage1_test_revisedwheredate_received2016-08-01andDate_received2016-07-01andDiscount_ratelike%:%groupbyMerchant_idorderbyamountdesc)selectrow_number()over(orderbyamount),Merchant_id,amountfromamount;答案如下我们看到最左面那一列就是序号列名有点长我们可以给他取别名语句如下withamountas(selectMerchant_id,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))amountfromccf_offline_stage1_test_revisedwheredate_received2016-08-01andDate_received2016-07-01andDiscount_ratelike%:%groupbyMerchant_idorderbyamountdesc)selectrow_number()over(orderbyamountdesc)Id-- 取别名名称为Id,Merchant_id,amountfromamount;答案如下我们可以看到这样就给表格增加了序号。从1开始连续排列。以上就求出了按照优惠券总金额对商家的排名。题目只是要求找出最多的那我们就还可以把上面求出来的表格给它命名比如“amounts_rank”语句如下withamountas(selectMerchant_id,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))amountfromccf_offline_stage1_test_revisedwheredate_received2016-08-01andDate_received2016-07-01andDiscount_ratelike%:%groupbyMerchant_idorderbyamountdesc),amount_rankas(selectrow_number()over(orderbyamountdesc)Id-- 取别名名称为Id,Merchant_id,amountfromamount)selectid,Merchant_id,amountfromamount_rankwhereidin(1,2,3);大家可以看一下上面的语句。我是先查询出一张表格amount通过amount我又得出另一张表也就是amount_rank这张表就是增加了ID号码。通过这张有ID号码的表格我只选取ID号码为的商家也就是排名前三的商家。结果如下逻辑如上图大家可以看一下我这里嵌套了两层。当然最后一个select 语句可以不用给表格命名。如果说我依然要引用这个select语句形成的表格我还需要是as语句并给表格命名。一般来说我们不建议嵌套的层数太多一般最多三层。我们可以用这个方法求出发放优惠券张数最多的商家并取前三名。这样我们就有了两张表格总金额最多的前三名张数最多的前三名。我们在利用连接语句把这两张表格连接起来。连接起来的表格应该三行六列。语句如下withamountsas(selectMerchant_id,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))amountfromccf_offline_stage1_test_revisedwheredate_received2016-08-01andDate_received2016-07-01andDiscount_ratelike%:%groupbyMerchant_idorderbyamountdesc),amounts_rankas(selectMerchant_id,amount,dense_rank()over(orderbyamountdesc)asrank_amountfromamounts),end_amountas(selectMerchant_id,amount,rank_amountfromamounts_rankwhererank_amountin(1,2,3,4)),countsas(selectMerchant_id,count(Discount_rate)dis_countfromccf_offline_stage1_test_revisedwhereDate_received2016-07-01andDate_received2016-08-01andDiscount_rateisnotnullgroupbyMerchant_idorderbycount(Discount_rate)desc),count_rankas(selectMerchant_id,dis_count,dense_rank()over(orderbydis_countdesc)rank_coutfromcounts),end_countas(selectMerchant_id,dis_count,rank_coutfromcount_rankwhererank_coutin(1,2,3,4))selectend_count.*,end_amount.*fromend_countjoinend_amountonend_count.rank_coutend_amount.rank_amount;如上语句最终是两份表格“end_count”和“end_amount”相连接连接依据就是“end_count.rank_coutend_amount.rank_amount”。答案如下6.2总结各位这道题我们就算完全解决了。里面的with as用法大家一定要掌握。有 这个用法我们呢才能处理复杂的情况编写嵌套语句。各位老铁有什么疑问欢迎评论区留言。尤其是针对最后的那个end_count”和“end_amount”相连接这个有点难度欢迎大家提问。