编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE ods_lkxx (
	xh int,
    hbxx varchar(255),
	name varchar(255),
	aj_date varchar(255),
    sfz varchar(255),
    mdz varchar(255),
    IDcard varchar(255),
    birthday int,
    aj_time varchar(255),
    aj_states varchar(255),
    Djk varchar(255)
);

-- INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南');
-- INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽');
-- INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州');
-- INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州');
-- INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州');
-- INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '杭州');
-- INSERT INTO student VALUES (7, '孙七',  '张三家隔壁', '杭州');
-- INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '杭州');
-- INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州');
-- INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州');
 
--分别用mysql和Oracle 的SQL语句来统计2021年9月3号4点到9月4号4点每个航班已安检的有多少旅客人数
--Oracle
Select count(*) lkrs from ods_lkxx 
where to_date(aj_time,'yyyymmddhh24miss') >= to_date('2021-09-03 04:00:00','yyyy-mm-dd hh24:mi:ss') 
and to_date(aj_time,'yyyymmddhh24miss') < to_date('2021-09-04 04:00:00','yyyy-mm-dd hh24:mi:ss') 
And aj_states = '1'

--MySQL
Select count(*) lkrs from ods_lkxx 
where str_to_date(aj_time,'%Y%m%d%H%i%s') >= str_to_date('2021-09-03 04:00:00','%Y-%m-%d %H:%i:%s') 
and str_to_date(aj_time,'%Y%m%d%H%i%s') < str_to_date('2021-09-04 04:00:00','%Y-%m-%d %H:%i:%s') 
And aj_states = '1'

--查询2021年9月3号每个航班已安检的第一个旅客
Select * from (Select Substr(ffid,1,instr(ffid,'-')-1) hbh,name,
Row_number() over(partition by Substr(ffid,1,instr(ffid,'-')-1) order by aj_time ) rw
From ods_lkxx
Where aj_date = '20210903'
And aj_states = '1' ) where rw = 1

--统计2021年9月3号12岁以下和70岁以上的安检旅客人数
Select  
sum(case when to_number(to_char(sysdate,'yyyy')) - to_number(nl) >= 70 then 1 else 0 end)  seventy,
Sum(case when to_number(to_char(sysdate,'yyyy')) - to_number(nl) <= 12 then 1 else 0 end)  twelve 
From ods_lkxx
Where aj_date = '20210903'
And aj_states = '1'

--查询2021年9月3号安检人数的同比、环比
Select t1.aj_date,t1.lkrs,
case when  t2.lkrs = 0 or t2.lkrs is null then 0 else round(100*(t1.lkrs-t2.lkrs)/t2.lkrs,2) end tb,
case when  t3.lkrs = 0 or t3.lkrs is null then 0 else round(100*(t1.lkrs-t3.lkrs)/t3.lkrs,2) end hb
from (
Select aj_date,count(*) lkrs
From ods_lkxx Where aj_date = '20210903' And aj_states = '1' group by aj_date ) t1
Left join (
Select aj_date,count(*) lkrs
From ods_lkxx Where aj_date = '20200903' And aj_states = '1' group by aj_date ) t2
On to_Date(t1.aj_date,’yyyymmdd’) = add_months(to_date(t2.aj_date,’yyyymmdd’),+12)
Left join (
Select aj_date,count(*) lkrs
From ods_lkxx Where aj_date = '20210902' And aj_states = '1' group by aj_date ) t3
On to_Date(t1.aj_date,’yyyymmdd’) = to_date(t3.aj_date,’yyyymmdd’)+1

--统计2021年9月3号男性、女性安检旅客人数
Select sex,sum(lkrs) lkrs from (
select 
case when substr(idcard,19,1) in ('1','3','5','7','9') then '男'
     When substr(idcard,19,1) in ('2','4','6','8','0') then '女' end sex,
Count(*) lkrs
From ods_lkxx
Where aj_date = '20210903'
And aj_states = '1'
Group by substr(idcard,19,1) ) group by sex

--删除旅客信息表中重复数据
Delete  from  ods_lkxx  a
where  (a.ffid, a.name )  in
(select  ffid, name  from ods_lkxx
group  by  ffid, name having  count(*)  >  1 )  
and rowid  not  in (select min(rowid)  from ods_lkxx
group by Sffid, name having count(*) > 1);