Mysql sql语句积累

分组排序

#alter table zxfw_offline_qa add zx_number varchar(50) COMMENT '咨询编号，提问日期+提问顺序' AFTER id;

/*
select 
		(@i := case  when  @pre_consult_time=consult_time then @i + 1 else 1 end )  rownum,  qa.*,
		(@pre_consult_time:=consult_time)  
 from  zxfw_offline_qa qa, (SELECT  @i := 0, @pre_consult_time:='') AS a  
 group  by consult_time,id order by consult_time
 
 */
 
 
# 表增加字段
alter table zxfw_offline_qa add zx_number varchar(50) COMMENT '咨询编号，提问日期+提问顺序' AFTER id;


# 创建临时表
 
DROP TABLE IF EXISTS `offline_temp`;
CREATE TABLE `offline_temp`  (
  `id` int(11),
  `zx_number` varchar(50)
);
 
# 把分组排序的值插入临时表
insert into offline_temp
select b.id,CONCAT(b.consult_time,'-',b.rownum) zx_number
from
(
select 
		(@i := case  when  @pre_consult_time=consult_time then @i + 1 else 1 end )  rownum,  qa.id,qa.consult_time,
		(@pre_consult_time:=consult_time)  
 from  zxfw_offline_qa qa, (SELECT  @i := 0, @pre_consult_time:='') AS a  
 group by consult_time,id
 ) b;
 
 
# 更新线下咨询表 
 update zxfw_offline_qa set zx_number='';
 
 update zxfw_offline_qa a 
 inner join offline_temp b
 on a.id=b.id
 set a.zx_number=b.zx_number;
 
 
 #查询结果
 select * from zxfw_offline_qa ORDER BY consult_time;
 
 
                     

 

 

禅道统计工时

select u.realname as '姓名',t.worktimes as '工时' from (
select finishedBy,round(SUM(consumed),2) as worktimes from zt_task where LENGTH(finishedBy)>0 and project=18 GROUP BY finishedBy
) t,zt_user u where t.finishedBy=u.account order by worktimes;

 

随机100条

select * from tdw  ORDER BY RAND() LIMIT 100;

 

/*
UPDATE dati0917 dt
        INNER JOIN
    ygh_user u ON dt.idcards=u.ID_CARD_NUM
SET 
    dt.app_phone = u.LOGIN_NAME;
		
	*/

#select length(csfz) from hzbz;

#select LENGTH(cert_no) from weixin;


#select cert_no from weixin where LENGTH(cert_no)>18;


#SELECT count(0) as hznum from hzbz;
#SELECT count(0) as wxnum from weixin;
#SELECT count(0) as 51num from wuyi_card;
#SELECT count(0) as appnum from ygh;



#数据清理

#delete from wuyi_card where length(wuyi_card_no)=0;

#update weixin set cert_no=trim(cert_no);

#update hzbz set csfz=trim(csfz);

#update wuyi_card set cert_no=trim(cert_no);

#update ygh set ID_CARD_NUM=trim(ID_CARD_NUM);


#查询不正常身份证
#select cert_no as wx_sfz from weixin where LENGTH(cert_no)<>18 and LENGTH(cert_no)<>15;
#select csfz as hzbz_sfz from hzbz where LENGTH(csfz)<>18 and LENGTH(csfz)<>15;
#select cert_no as 51_sfz from wuyi_card where LENGTH(cert_no)<>18 and LENGTH(cert_no)<>15 and wuyi_card_no is not null;
#select ID_CARD_NUM as app_sfz from ygh where LENGTH(ID_CARD_NUM)<>18 and LENGTH(ID_CARD_NUM)<>15;



#查出重复数据

#select cert_no as wx_sfz from weixin where cert_no in (select cert_no from weixin group by cert_no having count(cert_no)>1);

#select csfz as hzbz_sfz from hzbz where csfz in (select csfz from hzbz group by csfz having count(csfz)>1);

#select cert_no as 51_sfz from wuyi_card where cert_no in (select cert_no from wuyi_card group by cert_no having count(cert_no)>1);

#select ID_CARD_NUM as app_sfz from ygh where ID_CARD_NUM in (select ID_CARD_NUM from ygh group by ID_CARD_NUM having count(ID_CARD_NUM)>1);


#删除不正常身份证

#delete from weixin where LENGTH(cert_no)<>18 and LENGTH(cert_no)<>15;
#delete from hzbz where LENGTH(csfz)<>18 and LENGTH(csfz)<>15;
#delete from wuyi_card where LENGTH(cert_no)<>18 and LENGTH(cert_no)<>15;
#delete from ygh where LENGTH(ID_CARD_NUM)<>18 and LENGTH(ID_CARD_NUM)<>15;


#删除重复数据，多执行几次

/*
DELETE FROM ygh 
WHERE id IN 
(SELECT id FROM (SELECT id FROM ygh GROUP BY ID_CARD_NUM HAVING count(*) > 1)e);
*/


#进行标识
#51工会卡认证

#update hzbz set wuyika='YES' WHERE csfz IN (select csfz from (select hzbz.csfz FROM hzbz,wuyi_card where hzbz.csfz=wuyi_card.cert_no) a);

#update hzbz set weixin='YES' WHERE csfz IN (select csfz from (select hzbz.csfz FROM hzbz,weixin where hzbz.csfz=weixin.cert_no) a);

#update hzbz set app='YES' WHERE csfz IN (select csfz from (select hzbz.csfz FROM hzbz,ygh where hzbz.csfz=ygh.ID_CARD_NUM) a);



#SELECT count(0) as 51RZ from hzbz where wuyika='YES';
#SELECT count(0) as WXRZ from hzbz where weixin='YES';
#SELECT count(0) as APPRZ from hzbz where app='YES';
#SELECT count(0) as ALLRZ from hzbz where app='YES' AND wuyika='YES' AND weixin='YES';


#按产业和区导出

/*
select DISTINCT(cxtbh) from hzbz;


select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='财贸';
select * from hzbz where cxtbh='文卫';
select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='海曙区';
select * from hzbz where cxtbh='海曙区';



select * from hzbz where cdwm='宁波市海曙区段塘街道洞桥社区';

*/



#update hzbz0527 set wuyika='YES' WHERE csfz IN (select csfz from (select hzbz0527.csfz FROM hzbz0527,wuyi_card where hzbz0527.csfz=wuyi_card.cert_no) a);

#update hzbz0527 set weixin='YES' WHERE csfz IN (select csfz from (select hzbz0527.csfz FROM hzbz0527,weixin0527 where hzbz0527.csfz=weixin0527.cert_no) a);

#update hzbz0527 set app='YES' WHERE csfz IN (select csfz from (select hzbz0527.csfz FROM hzbz0527,ygh where hzbz0527.csfz=ygh.ID_CARD_NUM) a);




#去除奇怪的空格
#update hzbz0527 set csfz=replace(csfz,'　','');

#update hzbz0527 set remark=null;


#标识错误身份证号

#update hzbz0527 set remark='身份证格式错误' where LENGTH(csfz)<>18 and LENGTH(csfz)<>15;



#标识重复身份证号

/*
update hzbz0527 set remark='身份证重复' where csfz in (
select a.hzbz_sfz from
(
	select csfz as hzbz_sfz from hzbz0527 where csfz in (select csfz from hzbz0527 group by csfz having count(csfz)>1)
) a);
*/

 

SELECT
count(status = 0 OR NULL) AS a,
count(status = 1 OR NULL) AS b,
count(status = 2 OR NULL) AS c,
count(status = 3 OR NULL) AS d,
count(status = 4 OR NULL) AS e
FROM table;

 OR NULL必须要有