mysql实现row_number的⽅法1、利⽤内联关系取出多组条数据最新的数据（即oracle的row_number概念）：
-- 筛选出的状态 inner join筛选出来的状态值
select t.flow_id,t.job_id,t.`status`,t.start_time
from (
SELECT flow_id,job_id,status,start_time,max(FROM_UNIXTIME(start_time/1000)) as max_start_time
from execution_jobs
-- where (flow_id='end_it_sda' or flow_id='end_paasjszb_sda')
where (flow_id='end_paasjszb_sda')
and start_time>1544976295990
-- and job_id like '%sda%'
group by job_id,status
) t,(
--内联⽤例
SELECT flow_id,job_id,`status`,start_time,max(FROM_UNIXTIME(start_time/1000)) as max_start_time
from execution_jobs
-- where (flow_id='end_it_sda' or flow_id='end_paasjszb_sda')
where (flow_id='end_paasjszb_sda')
and start_time>1544976295990
-- and job_id='it_sda01_jiraissue'
group by job_id
) t1
where t1.max_start_time=t.max_start_time and t1.job_id=t.job_id
2、按照分区来查mysql数据：
select etl_tx_dt ,count(*) from zcfw_sda.sda01_core_asset_bad_out_info group by etl_tx_dt ;
3、启动django项⽬命令：django-admin startproject HelloWorld
4、通过分组确定唯⼀标识：
select count(*),core_lend_request_id,lend_contract_code,loan_time
from datawindow_dw.finance_report_defferred_income_detail_2
where etl_tx_dt=20181208
group by core_lend_request_id,lend_contract_code,loan_time
having count(*)>1;
5、分组按照⽇期排序：
select etl_tx_dt ,count(*) from hhpt_sda.sda01_btows_batch_mobile_20181210 group by etl_tx_dt order by etl_tx_dt desc;
6、数据库操作：
（1）模糊匹配查出表execution_jobs下flow_id有sda名字的
SELECT distinct flow_id from execution_jobs
where execution_jobs.flow_id like 'sda%';
（2）模糊匹配查出表projects下有sda_pro名字并且active=1的所有信息
select *
from projects p
where p.active = 1
and p.name like '%sda_pro';
（3）从表execution_jobs出10个时间戳为秒级别的从⼤到⼩的排序
select FROM_UNIXTIME(el.start_time/1000),el.*
from execution_jobs el
order by FROM_UNIXTIME(el.start_time/1000) desc
limit 10
（4）从表execution_jobs出时间戳⼤于2018-12-12的所有信息
select FROM_UNIXTIME(el.start_time/1000),el.*
from execution_jobs el
where FROM_UNIXTIME(el.start_time/1000) > '2018-12-12'
（5）统计表execution_jobs中时间戳⼤于2018-12-12的条数
select count(*)
from execution_jobs el
where FROM_UNIXTIME(el.start_time/1000) > '2018-12-12';
（6）从表projects中出时间戳⼤于2018-12-12，active=1和name中有sda_pro的任务列表，其中匹配条件为表project的id等于表execution_jobs的id
select p.`name`,el.job_id
from projects p,execution_jobs el
where p.active = 1
and p.name like '%sda_pro'
and p.id = el.project_id
and FROM_UNIXTIME(el.start_time/1000) > '2018-12-12'
group by p.`name`,el.job_id
order by p.`name`,el.job_id;
（7）从表projects中出时间戳⼤于2018-12-12，active=1和name中有sda_pro，并且execution_jobs表中job_id也有sda_pro的任务列表，其中匹配条件为表project的id等于表execution_jobs的id
select p.`name`,el.job_id
from projects p,execution_jobs el
mysql group by order bywhere p.active = 1
and p.name like '%sda_pro'
and p.id = el.project_id
and FROM_UNIXTIME(el.start_time/1000) > '2018-12-12'
and el.job_id like '%sda%'
group by p.`name`,el.job_id
order by p.`name`,el.job_id;
7、时间戳与⽇期的相互转换：
-- 时间戳转换成时间格式
select FROM_UNIXTIME(1544991635000/1000)
-- ⽇期转换成时间戳
SELECT UNIX_TIMESTAMP('2018-12-22 00:00:00') *1000

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。