随笔记录

最新推荐文章于 2025-02-11 11:02:33 发布

「已注销」

最新推荐文章于 2025-02-11 11:02:33 发布

阅读量307

点赞数

CC 4.0 BY-SA版权

分类专栏： selfdev

本文链接：https://blog.youkuaiyun.com/ihuning/article/details/49894439

selfdev 专栏收录该内容

5 篇文章

订阅专栏

数据表设计




create table t_article (
    article_id integer,
    article_title varchar2(200),
    article_content varchar2(2000),
    createdate date,
    updatedate date
)

create table t_article_index(
    article_id integer,
    index_updatedate date
)

insert into t_article values (1,'title1','content1',sysdate,sysdate);
insert into t_article values (2,'title2','content2',to_date('2015-10-17 22:00:53','yyyy-MM-dd hh24:mi:ss'),sysdate);
insert into t_article values (3,'title3','content3',sysdate,to_date('2015-05-01 22:00:53','yyyy-MM-dd hh24:mi:ss'));
insert into t_article values (4,'title4','content4',sysdate,to_date('2015-07-01 22:00:53','yyyy-MM-dd hh24:mi:ss'));
insert into t_article values (5,'title5','content5',sysdate,to_date('2015-09-01 22:00:53','yyyy-MM-dd hh24:mi:ss'));


insert into t_article_index values (3,sysdate)
insert into t_article_index values (5,to_date('2014-09-01 22:00:53','yyyy-MM-dd hh24:mi:ss'))

select * from t_article;
select * from t_article_index;



select a.article_id,a.article_title,a.article_content,a.updatedate,ai.article_id as article_index_id,ai.index_updatedate from t_article a
    left join t_article_index ai on a.article_id = ai.article_id 
    order by a.article_id

select a.article_id,a.article_title,a.article_content,a.updatedate,ai.article_id as article_index_id,ai.index_updatedate from t_article a
    left join t_article_index ai on a.article_id = ai.article_id 
    where ai.article_id is null or a.updatedate > ai.index_updatedate
    order by a.article_id
    
search_stock_article_index