根据月份比较并获取插入表中的新数据 [英] Compare and get the new data inserted into table based on month

问题描述

我有FM_TBL表，该表的month_id列是数字数据类型，日期以"YYYYMM"格式存储.

I have FM_TBL table which has month_id column which is number data type and dates are stored in this in format 'YYYYMM'.

我想比较上个月和当月的数据，并以此为基础找出当月插入FM_TABLE的新行数.

I want to compare the data from previous month and current month and based on this find out the number of new rows inserted into FM_TABLE in the current month.

推荐答案

您可以使用减号

select SYS_DB_NAME, ENTITY_ID, MONTH_ID
from my_table 
where MONTH_ID = to_char(sysdate, 'YYYY') || lpad( extract(month from sysdate), 2,'0')
minus  
select  SYS_DB_NAME, ENTITY_ID, MONTH_ID
from my_table 
where MONTH_ID = to_char(sysdate, 'YYYY') || lpad( (extract(month from sysdate) -1) , 2,'0') 

，如果您需要行内容

select * from  my_table  m
inner join  (
    select SYS_DB_NAME, ENTITY_ID, MONTH_ID
    from my_table 
    where MONTH_ID = to_char(sysdate, 'YYYY') || lpad( extract(month from sysdate), 2,'0')
    minus  
    select  SYS_DB_NAME, ENTITY_ID, MONTH_ID
    from my_table 
    where MONTH_ID = to_char(sysdate, 'YYYY') || lpad( (extract(month from sysdate) -1) , 2,'0')
) T on m.SYS_DB_NAME = t.SYS_DB_NAME 
      AND m.ENTITY_ID = t.ENTITY_ID 
        AND m.MONTH_ID = t.MONTH_ID

，如果您只需要计数

select count(*) from  
inner join  (
    select SYS_DB_NAME, ENTITY_ID, MONTH_ID
    from my_table 
    where MONTH_ID = to_char(sysdate, 'YYYY') || lpad( extract(month from sysdate), 2,'0')
    minus  
    select  SYS_DB_NAME, ENTITY_ID, MONTH_ID
    from my_table 
    where MONTH_ID = to_char(sysdate, 'YYYY') || lpad( (extract(month from sysdate) -1) , 2,'0')
) T

这篇关于根据月份比较并获取插入表中的新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！