常用sql

--page--
jpa里面的，需要-1Pageable pageable = PageRequest.of(pageNumber - 1, pageSize, Sort.by("classId").descending());
RepositoryImpl里面的pageable不需要-1
 queryListForMySql(sql, pageable, BeanPropertyRowMapper.newInstance(DingStudentDto.class), params);

---找重名---
SELECT id,xm,lxdh,yyid ,count(xm) FROM
	`base_teacherinfo` 
WHERE	ljsc = 0 	AND xxjgid = 97 GROUP BY	xm HAVING COUNT(xm)>1

---多个数据合并在一列中，中间以逗号隔开---
SELECT bu.`name`,GROUP_CONCAT(br.`name`) roleName FROM base_user bu LEFT JOIN base_user_role bur ON bu.id=bur.userid LEFT JOIN base_role br ON bur.roleid = br.id GROUP BY bu.`name`
GROUP_CONCAT(score ORDER BY score desc)//合并的排序

---交并集---
List<Integer> ids = list.stream().filter(Objects::nonNull).map(BaseClassinfo::getId).collect(Collectors.toList());
List<Integer> old = Arrays.asList(1,2,3,4);
List<Integer> newl = Arrays.asList(1,2,3);//
Collection<Integer> sub = CollectionUtils.subtract(old, newl);
List<Long> dellist = new ArrayList<Long>(del);

---拼接字符串---
select concat('11','22','33');  //112233
concat('四川省凉山州',re.`name`) as region

---一个字段逗号隔开，分成多行---
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.org_id, ',', b.help_topic_id + 1), ',', - 1) org_id
FROM effect_activity a
JOIN mysql.help_topic AS b ON b.help_topic_id < (LENGTH(a.org_id) - LENGTH(
REPLACE(a.org_id, ',', '')) + 1)
WHERE a.id =4 AND a.deleted=0

---截取某个最后一个字符前的字符串---
REVERSE(SUBSTR(REVERSE(PACKAGE_NO) , INSTR(REVERSE(PACKAGE_NO),'-')+1))

-----祖先相关的-----
SELECT * FROM base_model_tree a WHERE EXISTS (
SELECT * FROM base_model_tree b WHERE b.subjectid=1 AND FIND_IN_SET(a.id,b.root_path) IN (2,3)
)

substring_index(str,",",count)
如果count是正数，那么就是从左往右数，第N个分隔符的左边的所有内容
如果count是负数，那么就是从右往左数，第N个分隔符的右边的所有内容

--@Query里面两个查询----
@Query(value = "select * from base_schoolinfo where FIND_IN_SET(zzjcbid,getChildList(:orgid)) and FIND_IN_SET(:xxlx, xxlx) and zzlx='06' and ljsc='0' ", 
countQuery = "select count(*) from base_schoolinfo where FIND_IN_SET(zzjcbid,getChildList(:orgid)) and FIND_IN_SET(:xxlx, xxlx) and zzlx='06' and ljsc='0' ", nativeQuery = true)
Page<BaseSchoolInfo> findByXxlx(@Param("orgid") Integer orgid, @Param("xxlx") String xxlx, Pageable pageable);