sql分组查询语句，拼接sql查询语句

Atokinesis

已于 2022-09-10 15:26:41 修改

阅读量278

点赞数

分类专栏：笔记文章标签： sql 数据库 mysql

于 2022-09-10 14:39:38 首次发布

本文链接：https://blog.csdn.net/a1556621537/article/details/126796108

版权

笔记专栏收录该内容

18 篇文章 0 订阅

订阅专栏

查出项目下的用户id，用户名并用 , 隔开

<select id="selectByAdminProjectIds" resultMap="ProjectUserResultMap"> 
	SELECT 
		c.project_id,c.project_name, c.deletion_state_code, 
		GROUP_CONCAT(a.id)user_id,GROUP_CONCAT(a.username) username 
	FROM 
		mon_project_info c 
	LEFT JOIN 
		mon_rel_prj_usr b 
	ON c.project_id=b.project_id and b.deletion_state_code = '0' 
	LEFT JOIN 
		mon_user_account a 
	ON b.user_id=a.id and a.is_enabled='0'
		<where> 
			<if test="projectName != null and projectName != ''"> 
				and c.project_name like concat('%',#{projectName},'%') 
			</if> 
				and c.deletion_state_code = '0' 
		</where>
	 	GROUP BY c.project_id,c.project_name 
	 	order by c.project_id desc 
</select>

serviceImpl文件

String insertSql = "insert into " + tableName + " ( " + fieldName + " ) values ( " + valueStrings + " ) ";
String updateSql = "update " + tableName + " set " + updateStrings + " where " + key + " = " + keyValue; 
String checkSql = "select count(1) from " + tableName +" where "+ key +" = "+ keyValue ;

mapper文件，拼接查询语句后传入即可

Integer checkSql(@Param("sql")String checkSql);
boolean updateSql(@Param("sql") String updateSql); 
boolean insertSql(@Param("sql") String insertSql);

xml文件

<select id="checkSql" parameterType="String" resultType="integer"> 
		${sql} 
</select>

<update id="updateSql" parameterType="String"> 
		${sql} 
</update> 

<insert id="insertSql" parameterType="String"> 
		${sql} 
</insert>