mysql查询：查询嵌套（？）

Question

hi here's my data structure:

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

i want to output: all categories and each category should show:

• amount of images
• maximum amount of views
• date of latest image
• id of latest file

desired output:

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

my query so far:

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

the problem is: how would i get the last field id_latest?

thanks

Answer 1

Your tables are must be this :

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

images
imgid   name   catid  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

---

And code :

 Select
  categories.catid,
  categories.name,
  Count(images.imgid) As images,
  Sum(images.views) As views,
  Max(images.datetime) As latest

From
  categories 
  Left Join images On images.catid =  categories.catid 
Group By
  categories.catid, categories.name