分享10个高级sql写法

　　本文主要介绍博主在以往开发过程中，对于不同业务所对应的sql写法进行归纳总结而来。进而分享给大家。本文所讲述sql语法都是基于MySql8。0博主github地址：http：github。comwayn111欢迎大家关注，点个star一、ORDERBYFIELD（）自定义排序逻辑
　　MySql中的排序ORDERBY除了可以用ASC和DESC，还可以通过ORDERBYFIELD（str，str1，。。。）自定义字符串数字来实现排序。这里用orderdiy表举例，结构以及表数据展示：
　　ORDERBYFIELD（str，str1，。。。）自定义排序sql如下：SELECTfromorderdiyORDERBYFIELD（title，九阴真经，降龙十八掌，九阴白骨爪，双手互博，桃花岛主，全真内功心法，蛤蟆功，销魂掌，灵白山少主）；
　　查询结果如下：
　　如上，我们设置自定义排序字段为title字段，然后将我们自定义的排序结果跟在title后面。二、CASE表达式
　　casewhenthenelseend表达式功能非常强大可以帮助我们解决ifelseifelse这种问题，这里继续用orderdiy表举例，假如我们想在orderdiy表加一列level列，根据money判断大于60就是高级，大于30就是中级，其余显示低级，sql如下：SELECT，casewhenmoney60then高级whenmoney30then中级else低级ENDlevelfromorderdiy；
　　查询结果：
　　三、EXISTS用法
　　我猜大家在日常开发中，应该都对关键词exists用的比较少，估计使用in查询偏多。这里给大家介绍一下exists用法，引用官网文档：
　　可知exists后面是跟着一个子查询语句，它的作用是根据主查询的数据，每一行都放到子查询中做条件验证，根据验证结果（TRUE或者FALSE），TRUE的话该行数据就会保留，下面用emp表和dept表进行举例，表结构以及数据展示：
　　计入我们现在想找到emp表中deptname与dept表中deptname对应不上员工数据，sql如下：SELECTfromempewhereexists（SELECTfromdeptpwheree。deptidp。deptidande。deptname！p。deptname）
　　查询结果：
　　我们通过exists语法将外层emp表全部数据放到子查询中与一一与dept表全部数据进行比较，只要有一行记录返回true。画个图展示主查询所有记录与子查询交互如下：
　　第一条记录与子查询比较时，全部返回false，所以第一行不展示。第二行记录与子查询比较时，发现销售部门与dept表第二行销售部对应不上，返回true，所以主查询该行记录会返回。第二行以后记录执行结果同第一条。四、GROUPCONCAT（expr）组连接函数
　　GROUPCONCAT（expr）组连接函数可以返回分组后指定字段的字符串连接形式，并且可以指定排序逻辑，以及连接字符串，默认为英文逗号连接。这里继续用orderdiy表举例：sql如下：SELECTname，GROUPCONCAT（titleORDERBYiddescSEPARATOR）fromorderdiyGROUPBYnameORDERBYNULL；
　　查询结果：
　　如上我们通过GROUPCONCAT（titleORDERBYiddescSEPARATOR）语句，指定分组连接title字段并按照id排序，设置连接字符串为。五、自连接查询
　　自连接查询是sql语法里常用的一种写法，掌握了自连接的用法我们可以在sql层面轻松解决很多问题。这里用tree表举例，结构以及表数据展示：
　　tree表中通过pid字段与id字段进行父子关联，假如现在有一个需求，我们想按照父子层级将tree表数据转换成一级职位二级职位三级职位三个列名进行展示，sql如下：SELECTt1。jobname一级职位，t2。jobname二级职位，t3。jobname三级职位fromtreet1jointreet2ont1。idt2。pidleftjointreet3ont2。idt3。pidwheret1。pid0；
　　结果如下：
　　我们通过treet1jointreet2ont1。idt2。pid自连接展示一级职位二级职位，再用leftjointreet3ont2。idt3。pid自连接展示二级职位三级职位，最后通过where条件t1。pid0过滤掉非一级职位的展示，完成这个需求。
　　六、更新emp表和dept表关联数据
　　这里继续使用上文提到的emp表和dept表，数据如下：
　　可以看到上述emp表中jack的部门名称与dept表实际不符合，现在我们想将jack的部门名称更新成dept表的正确数据，sql如下：updateemp，deptsetemp。deptnamedept。deptnamewhereemp。deptiddept。deptid；
　　查询结果：
　　我们可以直接关联emp表和dept表并设置关联条件，然后更新emp表的deptname为dept表的deptname。七、ORDERBY空值NULL排序
　　ORDERBY字句中可以跟我们要排序的字段名称，但是当字段中存在null值时，会对我们的排序结果造成影响。我们可以通过ORDERBYIF（ISNULL（title），1，0）语法将null值转换成0或1，来达到将null值放到前面还是后面进行排序的效果。SELECTFROMtestrollupORDERBYIF（ISNULL（title），0，1），money；
　　查询结果：
　　image。png八、withrollup分组统计数据的基础上再进行统计汇总
　　MySql中可以使用withrollup在分组统计数据的基础上再进行统计汇总，即用来得到groupby的汇总信息。这里继续用orderdiy表举例，sql如下：SELECTname，SUM（money）asmoneyFROMorderdiyGROUPBYnameWITHROLLUP；
　　查询结果：
　　可以看到通过GROUPBYnameWITHROLLUP语句，查询结果最后一列显示了分组统计的汇总结果。但是name字段最后显示为null，我们可以通过coalesce（）比较函数，返回第一个非空参数。SELECTcoalesce（name，总金额）name，SUM（money）asmoneyFROMorderdiyGROUPBYnameWITHROLLUP；
　　查询结果：
　　image。png九、withas提取临时表别名
　　withas语法需要MySql8。0以上版本，它的作用主要是提取子查询，方便后续共用，更多情况下会用在数据分析的场景上。
　　如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用withas，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用。这里继续用orderdiy表举例，这里使用withas给出sql如下：使用withaswitht1as（SELECTfromorderdiywheremoney30），t2as（SELECTfromorderdiywheremoney60）SELECTfromt1wheret1。idnotin（SELECTidfromt2）andt1。name周伯通；
　　查询结果：
　　这个sql查询了orderdiy表中money大于30且小于等于60之间并且name是周伯通的记录。10、存在就更新，不存在就插入
　　MySql中通过onduplicatekeyupdate语法来实现存在就更新，不存在就插入的逻辑。插入或者更新时，它会根据表中主键索引或者唯一索引进行判断，如果主键索引或者唯一索引有冲突，就会执行onduplicatekeyupdate后面的赋值语句。这里通过news表举例，表结构和说数据展示，其中newscode字段有唯一索引：
　　添加sql：第一次执行添加语句INSERTINTOnews（newstitle，newsauth，newscode）VALUES（新闻3，小花，wx0003）onduplicatekeyupdatenewstitle新闻3；第二次执行修改语句INSERTINTOnews（newstitle，newsauth，newscode）VALUES（新闻4，小花，wx0003）onduplicatekeyupdatenewstitle新闻4；
　　结果如下：
　　总结
　　到这里，本文所分享的10个高级sql写法就全部介绍完了，希望对大家日常开发sql编写有所帮助，喜欢的朋友们可以点赞加关注。