sql

1.***02数据表

create table ***02  as
select line_no lineno,is_up_down isupdown,label_no labelno,station_id stationid,station_name stationname,
allot_time ALLOTTIME,hour,up_passenger UPPASSENGER,down_passenger DOWNPASSENGER,
--=line_no||is_up_down||allot_time||hour||station_id rowkey
reverse(allot_time||hour)||is_up_down||lpad(line_no,4,0)||station_id rowkey
from tm_ana_station_passenger_hour@conn_24268 where rownum <= 1000000
;

 2.如果带有时间的要特殊处理

 create table kkk07  as
select line_no lineno,is_up_down isupdown,label_no labelno,station_id stationid,station_name stationname,
up_passenger UPPASSENGER,down_passenger DOWNPASSENGER,ins_time instime,
--=line_no||is_up_down||allot_time||hour||station_id rowkey
reverse(to_char(ins_time,'yyyymmddhh24miss'))||lpad(line_no,4,0)||is_up_down rowkey
from TM_ANA_STA_PASSENGER_HIS;

 3.日期带有时分秒的统计，日期转换

select distinct to_char(INSTIME,'YYYYmmdd ')  as addTime from *K*05  order by addTime desc 

 4.简单日期统计

select distinct INS_TIME from TM_ANA_STA_PASSENGER_HIS order by INS_TIME desc 

 5.根据带时间的日期查询

select * from ***05 where   lineno='B601' and ISUPDOWN='0' and to_char(INSTIME,'YYYYmmdd ') =20180412 

 6.phoenix根据时间区间查询

select * from K**07 where "ROW"  between '214081020B60107%' and  '214081020B60109%'

 7.oracle时间区间查询

select * from TKKK05   where lineno='Y812' and isupdown='0' and allottime='20170511' and HOUR between '06' and '09'

 8.oracle多个时间段查询

方案一
    select *
      from T***05
     where lineno = '16Y'
       and isupdown = '1'
       and allottime = '20170501'
       and (HOUR between '00' and '06'
        or HOUR between '10' and '16'
        or HOUR between '20' and '24')
方案二
select * from T***05   where lineno='16Y' and isupdown='1' and allottime='20170501' and HOUR  not in ('07','08','09','17','18','19') 

 9.日期时间拆分

 create table ***05  as
select line_no lineno,is_up_down isupdown,label_no labelno,station_id stationid,station_name stationname,
up_passenger UPPASSENGER,down_passenger DOWNPASSENGER,ins_time instime,
--=line_no||is_up_down||allot_time||hour||station_id rowkey
reverse(to_char(ins_time,'yyyymmdd'))|| is_up_down||lpad(line_no,4,0)||to_char(ins_time,'hh24miss')|| lpad(label_no,2,0) rowkey
from ****HIS ;

10.数据分组排序

select CITY_NO,DEPT_NO,LINE_NO,MACH_NO,INS_TIME,ROW_NUMBER() over(order by INS_TIME desc)  from TM_BUS_RUN_INFO where line_no='K64'

 相同

select CITY_NO,DEPT_NO,LINE_NO,MACH_NO,INS_TIME  from TM_BUS_RUN_INFO where line_no='K64' order by   INS_TIME DESC

11.当很多字段相同的数据，就时间不同且只取最近时间的一条

select * from (select  CITY_NO,DEPT_NO,LINE_NO,MACH_NO,INS_TIME,ROW_NUMBER() over(partition by MACH_NO order by INS_TIME desc) as AAA  from TM_BUS_RUN_INFO ) where AAA=1

 12.SQL 如何查询某一张表某一字段重复次数，以及重复的字段值

 SELECT count(*), MACH_NO FROM *** GROUP BY  MACH_NO HAVING  count(*)>1