#拼装两个表进行查询
select total_stu.school_name,total_stu.total_stu,case when total_stu_ans.total_ans is null then 0 ELSE total_stu_ans.total_ans end as answered from
(select stu.middle_school_name as school_name,count(stu.student_code) as total_stu from test.student_info stu
GROUP BY stu.middle_school_name) as total_stu LEFT JOIN
(select stu.middle_school_name as school_name,count(stu.student_code) as total_ans from quesnaire_commit qc,common.user_info u,test.student_info stu
where qc.student_id = u.user_id and u.id_card_no = stu.student_code
GROUP BY stu.middle_school_name) as total_stu_ans
ON total_stu.school_name = total_stu_ans.school_name
ORDER BY answered desc

将一个表的字段更新到另一个表

update count c, student_info s
set c.school_name = s.primary_school_name
where c.student_code = s.student_code

杂七杂八

#select DISTINCT LEFT(student_code,1) from student_info s
#根据小学名称插入小学学校数据
#alter table school AUTO_INCREMENT=10000;
#insert into school(school_name,area_id,school_type) select distinct s.primary_school_name,2579,1010 from student_info s
#根据初中学校名称插入初中学校数据
#insert into school(school_name,area_id,school_type) select distinct s.middle_school_name,2579,1040 from student_info s
#&&&&&&&&&&&&&&&&&初中班级数据&&&&&&&&&&&&&&&&&&&&
#insert into school_class(school_id,session_id,class_type,class_no,class_name)
#select distinct sc.school_id,2017,1010,CONVERT(s.class_name,SIGNED) ,s.class_name from student_info s,school sc where s.middle_school_name = sc.school_name
#&&&&&&&&&&&&&&&&&小学班级数据&&&&&&&&&&&&&&&&&&&&
#根据className插入班级信息
#alter table school_class AUTO_INCREMENT=10000;
#insert into school_class(school_id,session_id,class_type,class_no,class_name)
#select distinct sc.school_id,2017,1010,CONVERT(s.class_name,SIGNED) ,s.class_name from student_info s,school sc where s.primary_school_name = sc.school_name
#根据学生信息插入学生信息
#alter table user_info AUTO_INCREMENT=200000;
#insert into user_info(real_name,gender,user_type,id_card_type,id_card_no,reg_type)
#select s.student_name,CASE WHEN s.gender = ‘男’ THEN 1 ELSE 2 END,1010,1030,TRIM(s.student_code),0 from student_info s
#根据学生信息生成登陆信息
#alter table user_info_auth AUTO_INCREMENT=200000;
#insert into user_info_auth(user_id,auth_type,identifier,credential)
#select u.user_id,1050,TRIM(s.student_code),’RiLva5wNGArwC5nmTopppdgyweFaMVs1′ from student_info s,user_info u
#where s.student_code = u.id_card_no
#alter table school AUTO_INCREMENT=1000;
#根据学生信息插入父亲家长信息
#insert into user_info(real_name,gender,user_type,id_card_type,id_card_no,reg_type)
#select CONCAT(LEFT(s.student_name,1),’爸爸’),1,1030,1030,TRIM(s.student_code),0 from student_info s
#根据家长信息生成登陆信息
#insert into user_info_auth(user_id,auth_type,identifier,credential)
#select u.user_id,1050,CONCAT(TRIM(s.student_code),’_parent’),’RiLva5wNGArwC5nmTopppdgyweFaMVs1′ from user_info u,student_info s
#where s.student_code = u.id_card_no and u.user_type = 1030 and u.id_card_type = 1030
#根据用户信息添加关联
#alter table parent_child_relation AUTO_INCREMENT=200000;
#INSERT parent_child_relation(parent_id,student_id,relation_type,create_time,update_time)
#select p.user_id,c.user_id,1010,NOW(),NOW() from user_info c,user_info p where
#c.user_type = 1010 and p.user_type = 1030 and c.id_card_no = p.id_card_no
#根据student_info已经生成的数据将学生关联到初中的班级
alter table parent_child_relation AUTO_INCREMENT=200000;
INSERT INTO student_class_relation(user_id,school_id,class_id,student_code,type)
select u.user_id,s.school_id,sc.class_id,u.id_card_no,1010 from school_class sc,school s,student_info stu ,user_info u
where s.school_id = sc.school_id
and stu.class_name = sc.class_name
and s.school_name = stu.middle_school_name
and u.id_card_no = stu.student_code
and u.user_type = 1030