按周分组1

SELECT TO_CHAR(to_date(replace(SUBSTR(t.hcaudtime, 1,10),'-',''),'yyyymmdd'),'IW') week  from houseselection t  
where t.hcsubtime is not null 
group by  TO_CHAR(to_date(replace(SUBSTR(t.hcaudtime, 1,10),'-',''),'yyyymmdd'),'IW')
order by TO_CHAR(to_date(replace(SUBSTR(t.hcaudtime, 1,10),'-',''),'yyyymmdd'),'IW')


效果 43 44 45   代表 1 2 3 

按周分组2

select * from (


(SELECT SUBSTR(t.inserttime,1,4) as yyyy,TO_CHAR(to_date((SUBSTR(t.inserttime, 1,4)||SUBSTR(t.inserttime, 6,2)||SUBSTR(t.inserttime, 9,2)),'yyyymmdd'),'IW') week  
,sum(t.squaremeter) as squaremeter
,sum(t.cmoney) as cmoney
,sum(t.yjfk) as yjfk
,sum(replace(t.cashMoney,'/','')) as cashMoney
,sum(replace(t.loansMoney,'/','')) as loansMoney
,nvl(sum(replace(t.fmoney,'/','')),0) as fmoney
      
from houseselection t  
--t.zt ='4'

group by  SUBSTR(t.inserttime,1,4),TO_CHAR(to_date((SUBSTR(t.inserttime, 1,4)||SUBSTR(t.inserttime, 6,2)||SUBSTR(t.inserttime, 9,2)),'yyyymmdd'),'IW')
order by SUBSTR(t.inserttime,1,4),TO_CHAR(to_date((SUBSTR(t.inserttime, 1,4)||SUBSTR(t.inserttime, 6,2)||SUBSTR(t.inserttime, 9,2)),'yyyymmdd'),'IW')
)) a
效果
1	2022	43	205.18	2387127	2362500	25031	0	268.94
5	2023	01	103.82	865501	840000	25501	0	0

查询相同企业

 select  a.name,a.idnumber from legalperson  a group by  name,a.idnumber  having count(*)>1;

数据库中随机查询10条数据

 SELECT * FROM (SELECT * FROM project_management  ORDER BY SYS_GUID()) WHERE ROWNUM <= 10;

插入所查询的语句

insert into bumenfankui select * from bumenfankui; 

一对多查询最新一条数据

使用：select * from (select b.*,(ROW_NUMBER() OVER (PARTITION BY b.审核记录id ORDER BY b.审核时间 desc))rn from 审核表 b) where rn = 1;获取多审核记录最新一条


利用 ROW_NUMBER()OVER 函数进行分类（PARTITION BY）排序（ORDER BY ），取出多方的最新一条数据进行展示。

例子：select *
from 新闻表 n
left join (select * from (select b. *, (row_number() over(partition by b.审核记录id  order by b.审核时间  desc)) rn from 审核表  b) e where rn = 1) t
on n.审核记录id= t.审核记录id
order by n.发布时间 desc;

横向查询拼接 图片名字也叫这个

//dense_rank() over(order by x.projectNature ) 把数据分组后 按照序号 123 列出 通过FULL JOIN on A.rn = c.rn 横向拼接
select * from
(select    x.projectNature ,count(x.projectNature) t_num ,dense_rank() over(order by x.projectNature ) as rn
from acceptcases x where   x.zt='1' and  x.projectNature is not null  group by x.projectNature ) A
FULL JOIN
(   select    x.enterpriseType,count(x.enterpriseType) e_num ,dense_rank() over(order by x.enterpriseType ) as rn
from acceptcases x where  x.zt='1' and  x.enterpriseType is not null group by x.enterpriseType  ) B
on A.rn = B.rn  
FULL JOIN
(   select    x.enterpriseType,count(x.enterpriseType) e_num ,dense_rank() over(order by x.enterpriseType ) as rn
from acceptcases x where  x.zt='1' and  x.enterpriseType is not null group by x.enterpriseType  ) c
on A.rn = c.rn

查扫码超过两次的人

select DISTINCT idcard  from (
select e.idcard 
from inoutrecored a inner join regpeople e on a.pid = e.id  where a.workstate = '1' and a.idcard = e.idcard
group by e.idcard,a.inoutstate HAVING COUNT(*)>=2) 

查询改错前的数据

select * from SYSTEM_MENU_INFO as of timestamp to_timestamp('2018-10-18 09:05:00','yyyy-mm-dd hh24:mi:ss');