有一些sql 是必须要做笔记的!!
2024-10-19 11:46:51
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 - 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+ ,
if(@pdept=b.zone and @aa=b.site,@rank:=@rank+,@rank:=) 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 := , @pdept := null , @aa := null ,@rank:=) 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 > (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名
最新文章
- 被误解的MVC和被神化的MVVM(转)
- Leetcode Copy List with Random Pointer
- SQL Server 参数化 PARAMETERIZATION
- 检测浏览器是否支持cookie方法
- [数据结构] N皇后问题
- ChinaASP.Upload 错误 &#39;80040002&#39; You must add our copyright info
- OpenXml Excel数据导入导出(含图片的导入导出)
- this point
- 前端SEO优化
- HYSBZ 2243	染色 (树链拆分)
- 基于nodejs模拟浏览器post请求爬取json数据
- [THUWC 2017]在美妙的数学王国中畅游
- Go 实现 NumberFormat 函数
- Vs Code 插件配置教程
- javascript 中的number
- SQLMap工具的安装使用
- echarts 响应式布局
- buildroot构建项目(二)--- u-boot 2017.11 建立 2440 开发板
- ffplay源码分析3-代码框架
- sencha touch TabPanel 加入导航按钮(向左向右按钮) 以及Carousel插件(2014-11-7)