用的比较少的sql示例

2023-03-25 05:14| 来源: 网络整理| 查看: 265

1.FIND_IN_SET(str，strList) 函数作用为 strList中是否包含str

GROUP_CONCAT(DISTINCT a) 将多行的a列上的参数拼接成一行用逗号隔开的串

连表查询时关联字段中有一个表的字段值为用逗号拼接的字符串可以用以下sql查询出来，其中 on 后面的 FIND_IN_SET() 函数会把所有对应的数据查询出来，不过显示出来的列表会已下面图片显示的样子

如图所见，本来是一条数据的，结果查询出来三条，那么就可以用到GROUP_CONCAT(DISTINCT a)函数了，这个函数的作用是把某字段的值合并成一个逗号隔开的字符串，执行完下面的sql展示出来的下面这个样子

SELECT f.ID,GROUP_CONCAT(DISTINCT p.POSITION_NAME) AS positionsName,p1.POSITION_NAME AS allotionEndName FROM functional_base f LEFT JOIN attachment a ON a.file_id = family_background_id LEFT JOIN position p ON FIND_IN_SET (p.POSITION_ID,f.POSITIONS) LEFT JOIN position p1 ON p1.POSITION_ID=f.ALLOTION_END

上面的sql适用于我的functional_base这个主表中只会有一条数据存在的情况下可以这样直接使用，但是大部分主表中不会只有一条数据，那样就会被GROUP_CONCAT(DISTINCT a)函数给合并成一条数据，这样的结果肯定不行，所以要给sql加一个group by 分组，建议分组用主表id来分，因为id是唯一的，可以达到最一开始的效果

其中DISTINCT 的作为为去重，如果逻辑中出现多个字段为多包含1的关系，那么两个GROUP_CONCAT()的参数会是重复的拼接，如果逻辑中有这样的操作，可以把DISTINCT 去掉尝试一下看看。

2.mapper.xml 中引用实体类方法

'${@[email protected]}' 的含义为实体类包中的 DefaultHead 类中的参数defaultHeadUrl 被引入进了下面方法中的 THEN 后面，程序中只要给defaultHeadUrl赋值就可以直接将值赋值到sql中。

SELECT CASE WHEN s.file_path IS NULL THEN '${@[email protected]}' WHEN s.staff_head IS NULL THEN '${@[email protected]}' ELSE s.file_path END AS staff_head FROM testTable s

3.如果想要根据不同的状态用一个sql计算出各种状态的sum，可以使用下面的方法，使用sum()和case when 来实现，下面的sql主要是根据 rewards_or_penalties 的不同状态，来计算不同状态下的 score 的总和，其中ifnull是将计算出来的null转换为0

SELECT IFNULL(SUM(CASE WHEN rewards_or_penalties=0 THEN score END),0) AS sumScore, IFNULL(SUM(CASE WHEN rewards_or_penalties=1 THEN score END),0) AS subScore FROM achievement_event WHERE 1=1

4.用sql子查询实现排名，其中的group by input_staff_id 中的input_staff_id，就是根据这个参数分组，然后进行排名，;为小于号,下面的sql语句，只是最基本的一个示例，可以根据自己的逻辑修改，得到的结果如果出现相同的数值那就是并列排名

SELECT a.*,( SELECT COUNT(DISTINCT column1) FROM ( SELECT SUM(column1) AS score FROM achievement_event WHERE approver_state=3 GROUP by input_staff_id) as b WHERE a.score ; b.score)+1 as rank FROM ( SELECT SUM(column1) AS score FROM sz_gf_achievement_event ae WHERE ae.approver_state=3 GROUP by ae.input_staff_id) AS a ORDER BY rank ASC

5.sql计算两个时间相差的天数，下面函数计算出来的方法值包含开始天数，结束时间不计算在内

DATEDIFF(endDate,startDate);

6.sql中if的使用，在某些特定的逻辑下if会比case when 要简单整洁的多，其中第一组数据为表达式，可以为多个，只要符合表达式则结果等于10，不符合表达式结果为20

if(a=b AND b=c AND 等等等,10,20)

7.mysql取字段逗号分隔的第一个，如a,b,c 串中取a（有两种方法）

主要用到的函数为（第一种方法）：

LOCATE(',',必须为需要处理的逗号隔开串的字段名)，该函数的作用就是定位该串中第一个逗号的位置

LEFT(需要截取的逗号隔开串的字段名,截取的位置），该函数的作用为左截取，从左边开始截取到想要的位置

下面这个sql的逻辑是因为需要操作的字段有可能只有一个数据，并没有多个逗号隔开，所以LOCATE()函数得到的位置是0，配合截取的时候就会出现问题，所以用if()函数判断一下，只有在多个逗号隔开的情况下才会操作截取，至于sql中的 ‘-1’ 是因为我们要截取的是不包含逗号在内的值（有LEFT()函数就有RIGHT()函数，意思是从右向左开始截取）

select a.row1,IF(a2.row2 IS NULL,'',row2 ) as row2 from table1 a1 LEFT JOIN table2 a2 on if(LOCATE(',',a1.str1)=0,a1.str1,LEFT(a1.str1,LOCATE(',',a1.str1)-1))=a2.str2 where 1=1

主要用到的函数为SUBSTRING_INDEX（为需要被处理的逗号隔开的字段名称,",",1）

select a.row1,IF(a2.row2 IS NULL,'',row2 ) as row2 from table1 a1 LEFT JOIN table2 a2 on SUBSTRING_INDEX（a1.row1,",",1）=a2.row1 where 1=1

8.使用sql进行批量插入，直接在sql内部进行循环相对比一直访问书库局来的要快一些

INSERT INTO table(id, field1, field2) VALUES (#{row.id}, #{row.field1}, #{row.field2})

9.使用sql将a表中的某个字段的数据同步到b表中某个字段（批量数据操作）,where 为两个表的关联字段

UPDATE `table1` AS t1, `table2` AS t2 SET t1.ziduan= t2.ziduan WHERE t1.ziduan_id= t2.ziduan_id

【本文地址】

公司简介

联系我们