mysql 拼装_Mysql 拼装两个表进行查询

#拼装两个表进行查询

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

©版权声明：本文为【翰林小院】(huhanlin.com)原创文章，转载时请注明出处！

发布在 2017-09-15 10:44:54

分类：MySQL, 关系型, 数据库

发表评论电子邮件地址不会被公开。

评论

姓名

电子邮件

站点