wp231957 发表于 2021-4-28 14:18:55

写了2个多小时的sql语句 脑袋嗡嗡的

truncate yuebao;
insert intoyuebao(成员,月完成) select 姓名 as 成员,sum(移网) from fazhanku where 部门="城市综合网格" and substring(日期,5,2)="04" group by 姓名;
update yuebao set 月指标=(select zhibiaoku.移网 from zhibiaoku where zhibiaoku.姓名=yuebao.成员);
update yuebao set 月完成率=月完成/`月指标`;
select DATEDIFF(date_add(curdate()-day(curdate())+1 ,interval 1 month ) ,DATE_ADD(curdate(),interval -day(curdate())+1 day)) into @daysa from dual;
select day(now()) into @days;
update yuebao set 序时指标=ceil(@days/@daysa*月指标) ;
update yuebao set 序时完成=月完成;
update yuebao set 序时完成率=序时完成/`序时指标`;
updateyuebao set `日指标` =(select 指标 from (select AA.成员 as 成员,CEIL((AA.`月指标`-BB.移网)/(@daysa-@days+1)) as 指标 from (select 成员,月指标 from yuebao GROUP BY 成员) as AA,(select 姓名,sum(移网) as 移网 from fazhanku where 部门="城市综合网格" and substring(日期,5,2)="04" and CONVERT(RIGHT(日期,2),SIGNED)<=@days-1 group by 姓名) as BB where AA.成员=BB.姓名) as CC where yuebao.`成员`=CC.成员);
update   yuebao set 日完成=( select sum(移网) from fazhanku where 部门="城市综合网格" and right(日期,2)=@days and fazhanku.`姓名`=yuebao.`成员`group by 姓名);
update yuebao set `日完成率`=`日完成`/`日指标`;
update yuebao set 日完成=0 , `日完成率`=0where 日完成 is null or `日完成率` is NULL;
updateyuebao set `明日指标` =(select 指标 from (select AA.成员 as 成员,CEIL((AA.`月指标`-BB.移网)/(@daysa-@days)) as 指标 from (select 成员,月指标 from yuebao GROUP BY 成员) as AA,(select 姓名,sum(移网) as 移网 from fazhanku where 部门="城市综合网格" and substring(日期,5,2)="04"group by 姓名) as BB where AA.成员=BB.姓名) as CC where yuebao.`成员`=CC.成员);

Daniel_Zhang 发表于 2021-4-28 14:44:31

大佬{:10_245:}

wp231957 发表于 2021-4-28 14:49:08

Daniel_Zhang 发表于 2021-4-28 14:44
大佬

啥大佬，我还不知道这些语句能不能被js 认可呢
现在暂时是MYSQL认可了

Daniel_Zhang 发表于 2021-4-28 14:55:13

wp231957 发表于 2021-4-28 14:49
啥大佬，我还不知道这些语句能不能被js 认可呢
现在暂时是MYSQL认可了

我现在只会一句了

select xxx from table{:10_250:}

wp231957 发表于 2021-4-28 15:14:53

Daniel_Zhang 发表于 2021-4-28 14:55
我现在只会一句了

select xxx from table

其实 百度能百到一小部分有用资料，但是鉴于sql语句你自己的需求和网上别人放出来的需求不可能完全一致
所以，很多核心语句也百不到，我一楼的语句都是实测通过的，一大半核心语句都是靠懵 懵过关的

wp231957 发表于 2021-4-29 10:28:15

update zhibiaoku set 部门=(select 部门 from denglu where denglu.`姓名`=zhibiaoku.`姓名`);

truncate yuebao;
insert intoyuebao(成员,月完成) select 部门 as 成员,sum(移网) from fazhanku wheresubstring(日期,5,2)="04" group by 部门;

update yuebao set 月指标=(select sum(zhibiaoku.移网) from zhibiaoku where zhibiaoku.部门=yuebao.成员);
update yuebao set 月完成率=月完成/`月指标`;

select DATEDIFF(date_add(curdate()-day(curdate())+1 ,interval 1 month ) ,DATE_ADD(curdate(),interval -day(curdate())+1 day)) into @daysa from dual;
select day(now()) into @days;
update yuebao set 序时指标=ceil(@days/@daysa*月指标) ;
update yuebao set 序时完成=月完成;
update yuebao set 序时完成率=序时完成/`序时指标`;
updateyuebao set `日指标` =(select 指标 from (select AA.成员 as 成员,CEIL((AA.`月指标`-BB.移网)/(@daysa-@days+1)) as 指标 from (select 成员,月指标 from yuebao GROUP BY 成员) as AA,(select 部门,sum(移网) as 移网 from fazhanku wheresubstring(日期,5,2)="04" and CONVERT(RIGHT(日期,2),SIGNED)<=@days-1 group by 部门) as BB where AA.成员=BB.部门) as CC where yuebao.`成员`=CC.成员);
update   yuebao set 日完成=( select sum(移网) from fazhanku where right(日期,2)=@days and fazhanku.`部门`=yuebao.`成员`group by 部门);
update yuebao set `日完成率`=`日完成`/`日指标`;

updateyuebao set `明日指标` =(select 指标 from (select AA.成员 as 成员,CEIL((AA.`月指标`-BB.移网)/(@daysa-@days)) as 指标 from (select 成员,月指标 from yuebao GROUP BY 成员) as AA,(select 部门,sum(移网) as 移网 from fazhanku wheresubstring(日期,5,2)="04"group by 部门) as BB where AA.成员=BB.部门) as CC where yuebao.`成员`=CC.成员);
update yuebao set 日完成=0 , `日完成率`=0 ,日指标=0,明日指标=0 where 日完成 is null or `日完成率` is NULL or 日指标<0 or 明日指标<0;
select * from yuebao;
页: [1]
查看完整版本: 写了2个多小时的sql语句 脑袋嗡嗡的