概述
1.插入
- 示例一：
- < trim prefix=“(” suffix=“)” suffixOverrides=“,”>
<insert id="insert" parameterType="xxx.xxx.entity.Cart">
insert into cart
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="userId != null">
user_id,
</if>
<if test="dealId != null">
deal_id,
</if>
<if test="dealSkuId != null">
deal_sku_id,
</if>
<if test="count != null">
count,
</if>
<if test="createTime != null">
create_time,
</if>
<if test="updateTime != null">
update_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=BIGINT},
</if>
<if test="userId != null">
#{userId,jdbcType=BIGINT},
</if>
<if test="dealId != null">
#{dealId,jdbcType=BIGINT},
</if>
<if test="dealSkuId != null">
#{dealSkuId,jdbcType=BIGINT},
</if>
<if test="count != null">
#{count,jdbcType=INTEGER},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
2.删除
- 示例一：
<delete id="delete" parameterType="java.lang.Long">
UPDATE doc_customize_user
SET is_delete = 1
WHERE id = #{id}
</delete>
3.更新
- 示例一：
<update id="updateByList">
UPDATE `follow_student`
<set>
folder_id = #{folderId},
update_id = #{userId},
update_time =#{time}
</set>
WHERE
subject_code = #{subjectCode,jdbcType=VARCHAR}
AND class_id = #{classId,jdbcType=BIGINT}
AND create_user_id = #{teacherId,jdbcType=BIGINT}
AND is_delete = 0
AND is_cancel = #{param5}
and follow_student_id in
<foreach collection="followStudentIds" item="id" index="index" open="(" close=")" separator=",">
#{id}
</foreach>
</update>
4.查询
- 示例一：
- < foreach collection=“classIds” item=“item” separator=“,” open=“(” close=“)”>
#{item}
< /foreach> - < if test=“getActivityBO.textbookId !=null and getActivityBO.textbookId !=‘’”>
AND t1.textbook_id = #{getActivityBO.textbookId}
< /if>
<select id="getActivity" resultType="xxx.xxx.VO.ActivityListVO">
SELECT
t2.activity_name,
t2.id AS activityId,
MIN( t3.push_time ) AS startTime,
MAX( t3.end_time ) AS endTime,
ROUND( AVG( t3.accuracy_rating ) * 100, 2 ) AS accuracy,
ROUND( SUM( t.time )/ COUNT( t.student_id ), 2 ) avgDuration,
FROM
`activity_knowledge` AS t1
INNER JOIN activity AS t2 ON t1.activity_id = t2.id AND t2.grade_id = #{getActivityBO.gradeId}
INNER JOIN activity_class AS t3 ON t1.activity_id = t3.activity_id
WHERE
t3. class_id IN
<foreach collection="classIds" item="item" separator="," open="(" close=")">
#{item}
</foreach>
<if test="knowledgeIds !=null and knowledgeIds.size>0">
AND t1.knowledge_id IN
<foreach collection="knowledgeIds" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
<if test="getActivityBO.textbookId !=null and getActivityBO.textbookId !=''">
AND t1.textbook_id = #{getActivityBO.textbookId}
</if>
<if test="getActivityBO.chapterId !=null and getActivityBO.chapterId !=''">
AND t1.chapter_id = #{getActivityBO.chapterId}
</if>
AND
t2.knowledge_source =#{source,jdbcType=INTEGER}
AND
t3.create_user_id = #{userId,jdbcType=BIGINT}
AND
t3.`status` = 2
AND
t1.is_delete =0
AND
t2.is_delete =0
AND
t3.is_delete =0
GROUP BY
t2.activity_name,activityId
HAVING AVG( t3.`status`)=2
</select>
- 示例二：
- 子查询（尽量避免）
<select id="selectListByClassIdsandActivityIds"
resultType="xxx.xxx.VO.WatchVideoVO">
SELECT
t.class_id,
t.activity_id,
ROUND( SUM( t.time )/ COUNT( t.student_id ), 2 ) avgDuration,
t.class_name ,
a.activity_name
FROM
(
SELECT
ROUND( SUM( slr.total_time )/ 60, 2 ) time,
slr.class_id,
slr.activity_id,
slr.student_id,
ac.class_name
FROM
student_link_resource slr
JOIN resource r ON r.id = slr.resource_id
AND r.type = 2
JOIN activity_class ac ON ac.class_id = slr.class_id
AND ac.activity_id = slr.activity_id
AND ac.is_delete = 0
AND ac.activity_type = #{activityType}
WHERE
slr.is_delete = 0
AND slr.activity_id IN
<foreach collection="activityIds" index="index" item="item" open="(" close=")" separator="," >
#{item}
</foreach>
AND slr.class_id IN
<foreach collection="classIds" index="index" item="item" open="(" close=")" separator="," >
#{item}
</foreach>
GROUP BY
slr.student_id,
slr.class_id,
slr.activity_id,
ac.class_name
) t
JOIN activity a ON a.id = t.activity_id
GROUP BY
t.class_id,
t.activity_id,
t.class_name,
a.activity_name
</select>
- 示例三：
- 子查询
<select id="getCorrectAnswerRate" resultType="java.math.BigDecimal">
SELECT FORMAT(
(SELECT COUNT(*)
FROM student_question_record
WHERE result = 1
<if test="activityId !=null and activityId.size>0">
AND activity_id IN
<foreach collection="activityId" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
AND student_id =#{studentId,jdbcType=BIGINT}
AND is_delete = 0
GROUP BY
student_id) / (SELECT COUNT(*)
FROM student_question_record
WHERE
student_id =#{studentId,jdbcType=BIGINT}
<if test="activityId !=null and activityId.size>0">
and activity_id IN
<foreach collection="activityId" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
AND is_delete = 0
GROUP BY
student_id) * 100,
2
)
</select>
- 示例四：
- < choose>
< when test=“pageBO.sort != ‘’ and pageBO.sort != null”>
< if test=“pageBO.sort == 1”>
ORDER BY sp.create_time ASC
< /if>
< /when>
< otherwise>
ORDER BY sp.create_time DESC
< /otherwise>
< /choose>
<select id="selectPaperPage" resultType="xxx.xxx.VO.SchoolPaperVO">
SELECT sp.*,
(CASE WHEN (sp.create_user_id != #{userId}) THEN 0 ELSE 1 END) isMyCreate
FROM
school_paper sp
LEFT JOIN school_paper_region spr ON sp.id = spr.school_paper_id AND spr.is_delete = 0
<where>
sp.is_delete = 0
AND sp.subject_code = #{pageBO.subjectCode}
<if test="pageBO.regionId != '' and pageBO.regionId != null">
AND spr.province_id = #{pageBO.regionId}
<if test="pageBO.cityId != '' and pageBO.cityId != null">
AND spr.city_id = #{pageBO.cityId}
</if>
</if>
<if test="pageBO.degreeId != '' and pageBO.degreeId != null">
AND sp.degree_id = #{pageBO.degreeId}
</if>
<if test="pageBO.gradeId != '' and pageBO.gradeId != null">
AND sp.grade_id = #{pageBO.gradeId}
</if>
<if test="pageBO.yearId != '' and pageBO.yearId != null and pageBO.yearId != 0">
AND sp.year_id = #{pageBO.yearId}
</if>
<if test="pageBO.yearId == 0">
AND sp.year_id < #{pageBO.minYearId}
</if>
<if test="pageBO.startTime != '' and pageBO.startTime != null and pageBO.endTime != '' and pageBO.endTime != null">
AND sp.create_time BETWEEN #{pageBO.startTime} AND #{pageBO.endTime}
</if>
<if test="pageBO.createUserName != '' and pageBO.createUserName != null">
AND sp.create_user_name LIKE CONCAT('%',#{pageBO.createUserName},'%')
</if>
<if test="pageBO.keywords != '' and pageBO.keywords != null">
AND sp.paper_name LIKE CONCAT('%',#{pageBO.keywords},'%')
</if>
</where>
GROUP BY sp.id
<choose>
<when test="pageBO.sort != '' and pageBO.sort != null">
<if test="pageBO.sort == 1">
ORDER BY sp.create_time ASC
</if>
<if test="pageBO.sort == 2">
ORDER BY sp.create_time DESC
</if>
</when>
<otherwise>
ORDER BY sp.create_time DESC
</otherwise>
</choose>
</select>
- 示例五：
- < trim prefix=“WHERE” suffixOverrides=“AND | OR”>
<select id="getClassActivityList" resultMap="classActivities">
SELECT * FROM (SELECT ac.id as ac_id, ac.class_id, ac.activity_id, ac.activity_type,
ac.status, a.activity_name, ac.create_time, ac.push_time, ac.end_time, a.remark
FROM `activity_class` ac
LEFT JOIN `activity` a ON ac.activity_id = a.id
AND a.is_delete = #{ci.isDelete} AND a.create_user_id = #{ci.userId}
<trim prefix="WHERE" suffixOverrides="AND | OR">
ac.class_id = #{ci.classId} AND
ac.subject_code = #{ci.subjectCode} AND
<if test="ci.activityIdSet != null and ci.activityIdSet.size() > 0">
<choose>
<when test="ci.activityIdSet.size() == 1">
<if test="ci.activityId != null">
ac.activity_id = #{ci.activityId} AND
</if>
</when>
<otherwise>
ac.activity_id in
<foreach collection="ci.activityIdSet" item="itemId" index="index"
open="(" close=")" separator=",">
#{itemId}
</foreach>
AND
</otherwise>
</choose>
</if>
<if test="ci.isFinished != null">
a.is_finished = #{ci.isFinished} AND
</if>
<if test="ci.activityType != null">
ac.activity_type = #{ci.activityType} AND
</if>
<if test="ci.activityStatus != null">
ac.status = #{ci.activityStatus} AND
</if>
ac.is_delete = #{ci.isDelete} AND ac.create_user_id = #{ci.userId}) AS r
ORDER BY @sort:=if(r.status = 1, -1,
if(r.status = 0, 0, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(r.end_time))),
r.push_time DESC, r.create_time DESC, r.end_time
</trim>
</select>
<resultMap id="classActivities"
type="xxx.xxx.VO.activity.ZzClassActivityVO">
<id property="activityClassId" column="ac_id"/>
<result property="classId" column="class_id"/>
<result property="activityId" column="activity_id"/>
<result property="activityName" column="activity_name"/>
<result property="activityType" column="activity_type"/>
<result property="classActivityStatus" column="status"/>
<result property="activityPushTime" column="push_time"/>
<result property="activityEndTime" column="end_time"/>
<result property="activityRemark" column="remark"/>
</resultMap>
- 示例六：
- DATE_SUB(CURDATE(), INTERVAL 7 DAY)
- timestamp(push_time)
<select id="teachingSituationPaging"
resultType="xxx.xxx.VO.LectureDetailsVO">
SELECT
t1.id,
t1.push_time as startTime,
t1.end_time,
t2.activity_name,
CEILING( t1.total_time / 60 ) totalTime
FROM
activity_class AS t1
INNER JOIN activity as t2 ON t1.activity_id = t2.id AND t2.subject_code = #{subjectCode}
WHERE
t1.activity_id IN (
SELECT activity_id
FROM activity_class
WHERE create_user_id = #{userId,jdbcType=BIGINT}
AND `status` = 2
AND subject_code = #{subjectCode}
<if test="type==0">
and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(push_time)
</if>
<if test="type==1">
and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(push_time)
</if>
<if test="type==2">
and timestamp(push_time) BETWEEN #{startTime,jdbcType=TIMESTAMP} and #{endTime,jdbcType=TIMESTAMP}
</if>
and is_delete =0
GROUP BY create_user_id )
AND t1.`status` = 2
AND t2.subject_code = #{subjectCode}
AND t2.is_delete = 0
ORDER BY t1.end_time desc
</select>
- 示例七：
- FORMAT( t2.toolSchoolAvg / t3.allNum, 2 ) AS toolSchoolAvg
- 自查询
<select id="toolDetails" resultType="xxx.xxx.VO.ToolDetailsVO">
SELECT
sdd.dict_label AS toolName,
COUNT(*) AS toolNum,
FORMAT( t2.toolSchoolAvg / t3.allNum, 2 ) AS toolSchoolAvg
FROM
activity_link AS t1
INNER JOIN ( SELECT link_type, COUNT(*) AS toolSchoolAvg FROM activity_link WHERE
is_delete=0 GROUP
BY link_type ) AS t2 ON t1.link_type = t2.link_type
INNER JOIN (
SELECT
t5.link_type,
COUNT(*) AS allNum
FROM
( SELECT link_type, COUNT(*) AS num FROM activity_link WHERE is_delete=0 GROUP BY
link_type,
create_user_id ) AS t5
GROUP BY
t5.link_type
) AS t3 ON t1.link_type = t3.link_type
INNER JOIN sys_dict_data sdd ON sdd.dict_value = t1.link_type
WHERE
activity_id IN (
SELECT activity_id
FROM activity_class
WHERE create_user_id = #{userId,jdbcType=BIGINT}
AND `status` = 2
AND subject_code = #{subjectCode}
<if test="type==0">
and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(push_time)
</if>
<if test="type==1">
and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(push_time)
</if>
<if test="type==2">
and timestamp(push_time) BETWEEN #{startTime,jdbcType=TIMESTAMP} and #{endTime,jdbcType=TIMESTAMP}
</if>
and is_delete =0
GROUP BY activity_id
)
AND t1.create_user_id = #{userId,jdbcType=BIGINT}
GROUP BY
t1.link_type
</select>
- 示例八：
- IF
( a.reuse_state = 0, a.id, a.is_reuse )
<select id="getDrafts"
resultType="xxx.xxx.VO.activity.DraftsVO">
SELECT
a.id activityId,
a.activity_name,
COUNT( al.id ) countNum,
a.is_reuse,
a.is_finished
FROM
`activity` a
LEFT JOIN activity_link al ON al.activity_id =
IF
( a.reuse_state = 0, a.id, a.is_reuse )
AND a.is_finished = #{gradeSubjectBO.isFinished}
AND al.is_delete = #{unfinished}
AND al.state != #{unfinished}
WHERE
a.subject_code = #{gradeSubjectBO.subjectId}
AND a.create_user_id = #{user.id}
AND a.is_delete = #{unfinished}
AND a.is_finished = #{gradeSubjectBO.isFinished}
AND a.grade_id = #{gradeSubjectBO.gradeId}
GROUP BY
a.id
ORDER BY
a.create_time DESC,
a.id
</select>
- 示例九：
- EXISTS(
SELECT 1
FROM courses_ready_group_user_relation crgr
INNER JOIN courses_ready_group crg ON crgr.group_id = crg.id AND crg.state = ‘NORMAL’ AND crg.is_delete = 0
WHERE crgr.user_id = #{params.userId}
AND crgr.join_state = ‘PASSING’ AND crgr.is_delete = 0 AND crg.id = clp.group_id
)
<select id="selectByPage" resultMap="getListEntity"
parameterType="xxx.xxx.entity.vo.TeamLessonTeamListsVO">
SELECT clp.id,
clp.`name`,
clp.type,
clp.start_time,
clpu.join_state,
clpu.is_host,
clpu.is_engage,
clp.`range`,
clp.group_id,
clp.initiator_id,
clp.subject_code,
<!--走索引,大可不必-->
( SELECT COUNT( clpur.id ) FROM collective_lesson_preparation_user_relation clpur WHERE clpur.ready_id = clp.id AND clpur.is_delete = 0 ) AS `count`
FROM collective_lesson_preparation clp
INNER JOIN collective_lesson_preparation_user_relation clpu ON clpu.ready_id = clp.id AND clpu.user_id = #{params.userId} AND clpu.is_delete = 0
<where>
<!--是否按照群组检索:(重构)-->
<if test="params.useGroup != null and params.useGroup">
EXISTS(
SELECT 1
FROM courses_ready_group_user_relation crgr
INNER JOIN courses_ready_group crg ON crgr.group_id = crg.id AND crg.state = 'NORMAL' AND crg.is_delete = 0
WHERE crgr.user_id = #{params.userId}
AND crgr.join_state = 'PASSING' AND crgr.is_delete = 0 AND crg.id = clp.group_id
)
</if>
<!--仅查看正常状态下的集备-->
AND clp.state = 'STATE_NORMAL'
AND clp.is_delete = 0
<!--名称:(重构)-->
<if test="params.name != null and params.name != ''">
AND clp.name LIKE concat('%', #{params.name}, '%')
</if>
<!--学科-->
<if test="params.subjectCode != null and params.subjectCode != ''">
AND clp.subject_code = #{params.subjectCode}
</if>
<!--类型-->
<if test="params.type != null and params.type != ''">
AND clp.type = #{params.type}
</if>
<!--方式-->
<if test="params.method != null and params.method != ''">
AND clp.method = #{params.method}
</if>
<!--范围-->
<if test="params.range != null and params.range != ''">
AND clp.method = #{params.range}
</if>
</where>
<!--按照创建时间倒序-->
ORDER BY clp.start_time DESC
</select>
最后
以上就是甜美美女为你收集整理的mysql 示例的全部内容，希望文章能够帮你解决mysql 示例所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复