Introduction to modifying associated variable conditions, querying and displaying data in one line in mysql

This article brings you an introduction to modifying associated variable conditions, querying and displaying data into one line in mysql (with code). It has certain reference value. Friends in need can refer to it. I hope it will help You helped.

One-to-many data is displayed in one row

GROUP_CONCAT(expr)

1. Involved table relationships: teacher table, teacher_subject_rel table (teacher table Table of subjects that can be taught), subject table
2, business scenario: It is necessary to retrieve the numbers (teacher_no) and subject names (subject_name) of all teachers.   The teacher table (teacher) and the subject (teacher_subject_rel) have a one-to-many relationship. Often, multiple pieces of data for the same teacher appear in the query. We hope to get each teacher's data subject to be spliced ​​into one

1. Basic syntax

group_concat( [DISTINCT] Fields to be connected [Order BY sorting field ASC/DESC] [Separator 'Separator symbol'] )

2. Example

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

##Subquery, query temporary table, 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_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 = '18501665888'
AND o.emergency_degree = 1
AND o. STATUS = 2
AND s.exam_year = '2015'
AND o.update_time >= '2018-08-14 20:28:55'
AND o.update_time = '2018-08-17 00:00:00'
AND a.first_time 
update Modification of associated variable conditions
1. Involved table relationships: id_number (ID card number) in the user_info table The birth field in the teacher table, the association relationship usrer_id = teacher_id
2. Business scenario: Get the birth date on the user’s ID card and update the birth date in the birth field 
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 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 > '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
 

#

The above is the detailed content of Introduction to modifying associated variable conditions, querying and displaying data in one line in mysql. For more information, please follow other related articles on the PHP Chinese website!