php - mysql 查询 : query nested (? )

Question

你好，这是我的数据结构:

categories
id   name
----------------
1    dogs    
2    cats
3    birds

images
id   name   cat  datetime     views
---------------------------------------
1    dog1   1    2015-10-01   123
2    dog2   1    2015-10-02   45
3    cat1   2    2015-10-03   678
4    cat2   2    2015-11-01   901
5    cat3   2    2015-11-02   234
6    bird1  3    2015-12-03   456

我要输出:所有类别和每个类别应显示:

• 图片数量
• 最大观看次数
• 最新图片的日期
• 最新文件的id

期望的输出:

cat  name     images  views   latest       id_latest
----------------------------------------------------
1    dogs     2       123     2015-10-02   5
2    cats     3       901     2015-11-02   4
3    birds    1       456     2015-12-03   6

到目前为止我的查询:

Select
  categories.*,
  Count(images.id) As images,
  Sum(images.views) As views,
  Max(images.datetime) As latest

From
  categories 
  Left Join images On images.catid =  categories.id 
Group By
  categories.id

问题是:我如何获得最后一个字段 id_latest？

谢谢

Answer 1

Select t.*, i.id as imageID 
  from 
    (Select 
        categories.*,
        Count(images.id) As images,
        Sum(images.views) As views,
        Max(images.datetime) As latest
      From categories 
      Left Join 
        images 
        On images.catid =  categories.id 
      Group By categories.id
    ) t
  left join 
    images i 
    on i.`datetime`= latest

example on sqlfiddle