mysql中修改关联变量条件、数据查询及显示成一行的相关操作

本篇文章给大家主要讲的是关于mysql中修改关联变量条件、数据查询及显示成一行的相关操作的内容，感兴趣的话就一起来看看这篇文章吧，相信看完mysql中修改关联变量条件、数据查询及显示成一行的相关操作对大家多少有点参考价值吧。

一对多数据显示成一行

GROUP_CONCAT(expr)

1、涉及的表关系:teacher表、teacher_subject_rel表(教师所能教的学科表)、subject表
2、业务场景: 需要拉取所有教师的编号(teacher_no)、学科名(subject_name)。   教师表(teacher)和学科(teacher_subject_rel)是一对多关系, 往往查询出现的是同一教师多条 数据。我们希望得到每个教师一条数据 学科拼接成一条

1、基本语法

group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符'] )

2、例子

SELECT    t.teacher_id as '教师id',    t.teacher_no '教师编号', (    SELECT        GROUP_CONCAT(s.subject_name)    FROM        teacher_subject_rel tsr    LEFT JOIN `subject` s ON tsr.subject_id = s.subject_id    WHERE        t.teacher_id = tsr.teacher_id) AS '学科'FROM    teacher t

子查询、查询临时表、EXISTS

例子

SELECT    *FROM    (        SELECT            o.id,            o.student_intention_id,            s. NAME,            s.area_id,            a.area_name,            s.exam_year,            o. STATUS,            CASE o. STATUS        WHEN '1' THEN            '待提交'        WHEN '2' THEN            '待指派'        WHEN '3' THEN            '已完成'        WHEN '4' THEN            '处理中'        END statusName,        CASE o.emergency_degree    WHEN '1' THEN        '正常'    WHEN '2' THEN        '紧急'    WHEN '3' THEN        '非常紧急'    END emergencyDegreeName,    o.emergency_degree,    o.update_time,    (        SELECT            first_lesson_time        FROM            jx_strategy        WHERE            jx_lesson_plan_order_id = o.id        AND STATUS IN (2, 7)        AND first_lesson_time > now()        ORDER BY            first_lesson_time ASC        LIMIT 1    ) AS first_time,    (        SELECT            deal_user_id        FROM            jx_strategy        WHERE            jx_lesson_plan_order_id = o.id        AND STATUS <> 7        AND deal_user_id <> 0        ORDER BY            id DESC        LIMIT 1    ) AS deal_user_idFROM    jx_lesson_plan_order oLEFT JOIN student s ON s.student_intention_id = o.student_intention_idLEFT JOIN area a ON s.area_id = a.idWHERE    o. STATUS <> 1AND s.phone = '18501665888'AND o.emergency_degree = 1AND o. STATUS = 2AND s.exam_year = '2015'AND o.update_time >= '2018-08-14 20:28:55'AND o.update_time <= '2018-08-14 20:28:55'    ) AS aWHERE    1 = 1AND a.deal_user_id = 145316AND a.first_time >= '2018-08-17 00:00:00'AND a.first_time <= '2018-08-30 00:00:00'AND EXISTS (    SELECT        *    FROM        jx_strategy js    WHERE        js.jx_lesson_plan_order_id = a.id    AND js. STATUS IN (2, 7)    AND js.subject_id IN (2, 3))ORDER BY    a.update_time DESCLIMIT 0, 10

update 关联变量条件修改

1、涉及的表关系: user_info表中的 id_number(身份证号) teacher表中的birth字段、 关联关系usrer_id = teacher_id
2、业务场景：获取用户身份证上的出生日期将出生日期更新在birth字段

UPDATE teacher t INNER JOIN (SELECT t.teacher_id, t.birth, u.id_number, CONCAT(SUBSTRING(u.id_number, 7, 4), '-', SUBSTRING(u.id_number, 11, 2), '-', SUBSTRING(u.id_number, 13, 2)) as birth2, u.reg_date, t.exit_time from teacher tINNER JOIN user_info u ON u.user_id = t.teacher_id) info on info.teacher_id = t.teacher_idSET t.birth = info.birth2WHERE info.reg_date > '2018-08-20 00:00:00' and info.id_number is not NULL and (info.birth is NULL or t.birth = '') and t.is_train = 1

以上关于mysql中修改关联变量条件、数据查询及显示成一行的相关操作详细内容，对大家有帮助吗？如果想要了解更多相关，可以继续关注我们的行业资讯板块。