sql技巧(增册改查)

select * from wyl.t; 2 --将数据从t1导入t2 3 insert into t2(c1,c2) select c1,c2 from t1 where c1= xx and c2 = xx order by c1; 4 --使用to表的name来更新t1表的name 5 update t1 as a,t2 as b set a.name = b.name where a.tid = b.id; 6 --两表关联更新 7 update t_role_user as a, 8 ( 9 select 10 id 11 from 12 t_user 13 where 14 departid in( 15 select 16 id 17 from 18 t_depart 19 where 20 length(org_code) = 9 21 ) 22 ) as b 23 set a.roleid = ‘12345‘ 24 where 25 a.userid = b.id; 26 --自己和自己关联一更新 27 update t_depart as a, 28 ( 29 select 30 id, 31 substring(org_code,1,6) org_code 32 from 33 t_depart 34 where 35 length(org_code)=8 36 and parent_depart_id is not null 37 ) as b 38 set a.parent_depart_id = b.id 39 where 40 substring(a.org_code,1,6) = 41 b.org_code 42 --两表关联删除，将删除两表中关联id并且t2表name为空的两表记录 43 delete a,b from t1 as a left join t2 as b on a.tid = b.id where b.name is null; 44 --奖统计结果插入到表 45 insert into se_stat_org ( 46 record_date, 47 org_id, 48 org_name, 49 sign_cont_count, 50 sign_arri_cont_count, 51 sign_cont_money, 52 sign_arri_cont_money, 53 total_arri_cont_count, 54 total_arri_money, 55 publish_total_count, 56 project_count 57 ) select 58 * 59 from 60 ( 61 select 62 ‘2012-06-09‘ record_date, 63 parent_org_id, 64 parent_org_name, 65 sum(sign_cont_count) sign_cont_count, 66 sum(sign_arri_cont_count) sign_arri_cont_count, 67 sum(sign_cont_money) sign_cont_money, 68 sum(sign_arri_cont_money) sign_arri_cont_money, 69 sum(total_arri_cont_count) total_arri_cont_count, 70 sum(total_arri_money) total_arri_money, 71 sum(publish_total_count) publish_total_count, 72 sum(project_count) project_count, 73 from se_stat_user 74 where date_format(record_date, ‘%y-%m-%d‘) = ‘2012-06-09‘ 75 group by parent_org_id 76 ) m 77 78 --三表关联更新 79 update se_stat_user a, 80 ( 81 select 82 user_id, 83 sum(invest_org_count + financial_org_count + intermediary_org_count + enterprise_count) as common_count 84 from se_stat_user 85 where date_format(record_date, ‘%y-%m-%d‘) = ‘2012-06-09‘ 86 group by user_id 87 ) b, 88 ( 89 select 90 user_id, 91 sum(establish_count + stock_count + merger_count + achieve_count) as project_count 92 from se_stat_user 93 where date_format(record_date, ‘%y-%m-%d‘) = ‘2012-06-09‘ 94 group by user_id 95 ) c 96 set a.common_count = b.common_count, a.project_count = c.project_count 97 where a.user_id = b.user_id 98 and a.user_id = c.user_id 99 and date_format(a.record_date, ‘%y-%m-%d‘) = ‘2012-06-09‘ 100 --带条件的关联更新 101 update se_stat_user a, 102 ( 103 select 104 p.channel, 105 count(p.cont_id) as cont_count, 106 c.cust_mgr_id 107 from 108 ( 109 select 110 channel, 111 cont_id 112 from sk_project 113 where project_status = 6 114 and date_format(audit_time, ‘%y-%m-%d‘) = ‘2012-06-11‘ 115 ) p 116 inner join se_contract c on p.cont_id = c.cont_id 117 group by p.channel, c.cust_mgr_id 118 ) b 119 set 120 a.stock_count = case when b.channel = 2 then b.cont_count else 0 end, 121 a.establish_count = case when b.channel = 3 then b.cont_count else 0 end, 122 a.achieve_count = case when b.channel = 4 then b.cont_count else 0 end, 123 a.brand_count = case when b.channel = 5 then b.cont_count else 0 end, 124 a.merger_count = case when b.channel = 6 then b.cont_count else 0 end 125 where 126 a.user_id = b.cust_mgr_id 127 and date_format(a.record_date, ‘%y-%m-%d‘) = ‘2012-06-11‘ 128 --加索引 129 alter table project add index index_user_id (user_id), 130 add index index_project_status (project_status); 131 --删除列 132 alter table project drop column project_status, 133 drop column expect_return,drop column currency; 134 --增加列 135 alter table project 136 add column dict_id int default null comment ‘xxx‘ after project_site, 137 add column introduce text default null comment ‘xx‘ after dict_id, 138 add column stage int default null comment ‘xx‘ after id, 139 add column attach_uri varchar(8) default null comment ‘xxx‘ after introduce; 140 --修改列，一般用modify修改数据类型，change修改列名 141 alter table project change dict_id dict_id1 int not null, 142 modify project_status tinyint not null comment ‘xxx‘; 143 --1.总体累计统计 对员工的工资，人数进行总体累计统计 144 select 145 employee_id, 146 sum(salary) over(order by employee_id) sal, 147 count(*) over (order by employee_id) num, 148 sum(salary) over() total_sal, 149 count(*) over() total_num, 150 from hr.employees; 151 --2.分组累计统计 对各个部门中的员工工资，人数进行分组累计统计 152 select 153 department_id, 154 employee_id, 155 sum(salary) over(partition by department_id order by employee_id) sal, 156 count(*) over(partition by department_id order by employee_id) sum 157 from hr.employees;

 

sql技巧(增册改查)

标签：部门   from   null   div   select   sel   delete   money   status