Modify the conditions of associated variables, data query and related operations displayed in a row in mysql

This article is mainly about mysql to modify related variable conditions, data query and display into a row of related operations, if you are interested, then take a look at this article, I believe that after reading the mysql to modify related variable conditions, data query and display into a row of related operations to you more or less reference value.

One-to-many data is displayed in a row

GROUP_CONCAT (expr)

1. The table relations involved: teacher table, teacher_subject_ rel table (the list of subjects that teachers can teach), subject table

2. Business scenario: you need to pull the numbers (teacher_no) and subject names (subject_name) of all teachers. & nbsp faculty table (teacher) and discipline (teacher_subject_rel) is an one-to-many relationship, often query multiple pieces of data from the same teacher. We hope to get each teacher a data subject to be spliced into one.

1. Basic grammar

Group_concat ([DISTINCT] the field to be connected [Order BY sort field ASC/DESC] [Separator 'delimiter'])

2. Examples

SELECT t.teacher_id as' teacher id', t.teacher_no 'teacher number, (SELECT GROUP_CONCAT (s.subject_name) FROM teacher_subject_rel tsr LEFT JOIN `instruct`s ON tsr.subject_id = s.subject_id WHERE t.teacher_id = tsr.teacher_id) AS' discipline 'FROM teacher t

Subquery, query temporary table, EXISTS

Examples

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'to be submitted 'WHEN' 2' THEN'to be assigned 'WHEN' 3' THEN 'completed' WHEN'4' THEN 'processing' END statusName CASE o.emergency_degree WHEN'1' THEN 'normal' WHEN'2' THEN 'urgent' WHEN'3' THEN 'very urgent' 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_idFROM 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 an 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-17 00:00:00'AND a.first_time '2018-08-20 00 and info.id_number is not NULL and (info.birth) Is NULL or t.birth ='') and t.is_train = 1

The above about the mysql to modify the conditions of related variables, data query and display into a row of related operation details, is it helpful for everyone? If you want to know more about it, you can continue to follow our industry information section.