客户需要做文档需要我们从系统中的文章和栏目表中给他们到处excel筛选所有栏目下面最后一个发布的文章的名称和发布时间并且输出父栏目的名称;后来考虑了首先需要从每个栏目中找出最后一条发布的文章
select
max(art_crt)
from article
group
by govl_id
其中art_crt为文章的创建时间,govl_id为栏目Id ,本来希望能查找出对应的文章id如下:
select
max(art_crt),art_id
as art_crt
from article
group
by govl_id
但是报了:“列 'article.Art_Id' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中”的错误所以只好放弃已art_crt作为连接条件最终的sql如下:
select c.govl_ttl
as
'
父标题
',b.govl_ttl
as
'
子标题
',a.art_atit
as
'
文章名称
',a.art_crt
as
'
创建时间
'
from
(
select
max(art_crt)
as art_crt
from article
group
by govl_id) d
left join article a left join govlist b on a.govl_id =b.govl_id
left join govlist c on b.govl_pat = c.govl_id
on a.art_crt =d.art_crt where a.art_atit <> '' order by b.govl_pat asc
left join article a left join govlist b on a.govl_id =b.govl_id
left join govlist c on b.govl_pat = c.govl_id
on a.art_crt =d.art_crt where a.art_atit <> '' order by b.govl_pat asc
虽然有点无语,但是起码解决了问题,时间精确到秒以后的话 误差应该很小。