Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.