Home >Database >Mysql Tutorial >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
ExampleSELECT * 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 <p>update Modification of associated variable conditions<strong></strong></p>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<p>2. Business scenario: Get the birth date on the user’s ID card and update the birth date in the birth field <br></p><pre class="brush:php;toolbar:false">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!