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 &＃39;分隔符&＃39;] )

2、例子

SELECT
    t.teacher_id as &＃39;教师id&＃39;,
    t.teacher_no &＃39;教师编号&＃39;,
 (
    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 &＃39;学科&＃39;
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 &＃39;1&＃39; THEN
            &＃39;待提交&＃39;
        WHEN &＃39;2&＃39; THEN
            &＃39;待指派&＃39;
        WHEN &＃39;3&＃39; THEN
            &＃39;已完成&＃39;
        WHEN &＃39;4&＃39; THEN
            &＃39;处理中&＃39;
        END statusName,
        CASE o.emergency_degree
    WHEN &＃39;1&＃39; THEN
        &＃39;正常&＃39;
    WHEN &＃39;2&＃39; THEN
        &＃39;紧急&＃39;
    WHEN &＃39;3&＃39; THEN
        &＃39;非常紧急&＃39;
    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_id
FROM
    jx_lesson_plan_order o
LEFT JOIN student s ON s.student_intention_id = o.student_intention_id
LEFT JOIN area a ON s.area_id = a.id
WHERE
    o. STATUS <> 1
AND s.phOne= &＃39;18501665888&＃39;
AND o.emergency_degree = 1
AND o. STATUS = 2
AND s.exam_year = &＃39;2015&＃39;
AND o.update_time >= &＃39;2018-08-14 20:28:55&＃39;
AND o.update_time <= &＃39;2018-08-14 20:28:55&＃39;
    ) AS a
WHERE
    1 = 1
AND a.deal_user_id = 145316
AND a.first_time >= &＃39;2018-08-17 00:00:00&＃39;
AND a.first_time <= &＃39;2018-08-30 00:00:00&＃39;
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 DESC
LIMIT 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), &＃39;-&＃39;, SUBSTRING(u.id_number, 11, 2), &＃39;-&＃39;, SUBSTRING(u.id_number, 13, 2)) as birth1, u.reg_date, t.exit_time from teacher t
INNER JOIN user_info u ON u.user_id = t.teacher_id

) info on info.teacher_id = t.teacher_id
SET t.birth = info.birth1
WHERE info.reg_date > &＃39;2018-08-20 00:00:00&＃39; and info.id_number is not NULL and (info.birth is NULL or t.birth = &＃39;&＃39;) and t.is_train = 1

以上就是mysql中关联变量条件修改、查询以及数据显示成一行的介绍的详细内容，更多请关注 第一PHP社区 其它相关文章！