有一些sql 是必须要做笔记的！！

select CONCAT(unix_timestamp(),"-",id,"-",name) as aa,age from workers;  //连接字段



select substr(concat("0000000",id),8,2) from workers //从第八位开始截2位
select right(concat("0000000",id),3) from workers //截取右边5位 截取左边5位改成left

　　

SELECT
	substr(comm, 1, locate('(', comm) - 1)
FROM
	groupcomm_sz
WHERE
	comm LIKE '%(%'
	
set @row=0;
SELECT module, machine, time, @row:=@row+1 rownum
FROM total_freq_ctrl
order by module,machine,time desc
limit 10;

　　

select a.city,a.comm,a.num,a.rank from (select a.city,a.comm,a.num,row_number() over (PARTITION by a.city ORDER BY a.num desc )as rank from (select city,comm,count(distinct urls)as num from salehouse_tmp where tosite = 'fang' and length(comm)>1 and length(comm)<12 and length(city)>1 group by city,comm)a)a where a.rank <7;

　　

select a.urls from (select urls from anjuke_info where date like '201707%' and type = 'SaleHouse' group by urls)a left outer join(select urls from rank_salehouse where web = 'anjuke' group by urls)b on (a.urls = b.urls) where b.urls is null;

　　

UPDATE wuhan_houseList SET  includeTime=DATE_ADD(includeTime,INTERVAL -1 DAY)

更新日期字段（字段为Date类型） 减去一天

 

select company,zone,num,rank from (  
select b.company,b.zone,b.num,@rownum:=@rownum+1 ,  
if(@pdept=b.zone,@rank:=@rank+1,@rank:=1) as rank,  
@pdept:=b.zone  
from (   
select company,zone,num from km_data order by zone asc ,num desc   
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result   


 

 

select company,zone,site,num,rank from (  
select b.company,b.zone,b.site,b.num,@rownum:=@rownum+1 ,  
if(@pdept=b.zone and @aa=b.site,@rank:=@rank+1,@rank:=1) as rank,  
@pdept:=b.zone,@aa:=b.site 
from (   
select company,zone,site,num from xinya_data_week where day = '2017-11-22' and tag = 'ZoneOnline' order by zone asc ,site asc,num desc   
) b ,(select @rownum :=0 , @pdept := null , @aa := null ,@rank:=0) a ) result 

根据2个字段排序

结果：

 

 

{$where:"this._id!=this.keys"} 

mongo 查询不等于

 

select zone,street,comm,towards,floors,floor,room,total,area from tmp_lianjia_1 as a where a.room <> '' and 2> (select count(*) from tmp_lianjia_1 where comm = a.comm and room = a.room and total < a.total  )  order by a.comm ,a.total ;


分组算前2名