我想查询一个主栏目下面的每个子栏目中最新的一条新闻
表结构:栏目表(channel)CHN_ID,CHN_NAME,PARENT_ID
新闻表(article)id,CHN_ID,TITLE,CREATE_TIME
表结构:栏目表(channel)CHN_ID,CHN_NAME,PARENT_ID
新闻表(article)id,CHN_ID,TITLE,CREATE_TIME
解决方案 »
- 问一个关于oracle事物的问题?(菜鸟级别的)
- 求一段oracle代码,循环插入数据
- 求解,怎样将.shp(shapefile)地图文件导入oracle数据库,非常感谢!
- 请问用oracle做后台,JSP为前台的WEB项目需要用连接池技术吗?
- 更新表table1的一个字段,如果还要和table2关联,这个sql语句该怎么写?
- 急 -- php连接Oracle数据库经常连接失败
- 我想创建一个用户,用该用户名登陆后只能看该用户名对应的模式
- 如何将VARCHAR型转成CLOB型?
- 有关排序 (回贴比有分)
- 两个子查询的连接查询如何提高查询速度
- 求助:表中有数据,使用OCI接口却查询不到!
- 请教各位大大,如果查找表中字段相连的记录?
新闻表(article)id,CHN_ID,TITLE,CREATE_TIME
from (select CHN_ID,id,
TITLE,
CREATE_TIME,
row_number() over(partition by CHN_ID order by id desc) r
from article)
where r = 1或
select *
from (select CHN_ID,id,
TITLE,
CREATE_TIME,
row_number() over(partition by CHN_ID order by CREATE_TIME desc) r
from article)
where r = 1参考 oracle 分析函数。 开窗函数。
select *
from (select CHN_ID,id,
TITLE,
CREATE_TIME,
row_number() over(partition by CHN_ID order by CREATE_TIME desc) r
from article where PARENT_ID=:PARENT_ID)
where r = 1
from (select CHN_ID,id,
TITLE,
CREATE_TIME,
row_number() over(partition by CHN_ID order by id desc) r
from article)
where r = 1 或
select *
from (select CHN_ID,id,
TITLE,
CREATE_TIME,
row_number() over(partition by CHN_ID order by CREATE_TIME desc) r
from article)
where r = 1
B.CHN_NAME 副栏目名,
T.TITLE,
T.CREATE_TIME
FROM ARTICLE T, CHANNEL A, CHANNEL B
WHERE T.CHN_ID = B.CHN_ID
AND B.PARENT_ID = A.CHN_ID
AND T.CREATE_TIME IN
(SELECT MAX(T.CREATE_TIME) FROM ARTICLE T GROUP BY T.CHN_ID)
select a.chn_id,a.chn_name,a.parent_id,b.id,b.title,b.create_time from
channel a left join article b
on a.chn_id=b.chn_id and
b.create_time is in(select max(create_time) from article group by chn_id)
将article表中每个栏目的最新新闻接在channel表对应的栏目后面