如何通过yii活动记录查询5个相册的最后一张图像 - how to query last image of 5 album by yii active record

我使用Yii框架来开发一个网站，我想知道如何通过使用Yii活动记录和普通SQL来获得由create_time排序的最后5张图像，但不在同一专辑内。

这是我的相册表:

CREATE TABLE IF NOT EXISTS `tbl_album` (
  `album_id` int(11) NOT NULL AUTO_INCREMENT,
  `album_name` varchar(45) DEFAULT NULL,
  `album_folder_name` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `create_user_id` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `update_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`album_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=55 ;

，这是我的图像表:

CREATE TABLE IF NOT EXISTS `tbl_image` (
  `image_id` int(11) NOT NULL AUTO_INCREMENT,
  `image_name` varchar(100) DEFAULT NULL,
  `image_description` varchar(100) DEFAULT NULL,
  `image_album_id` int(11) NOT NULL,
  `create_time` datetime DEFAULT NULL,
  `create_user_id` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `update_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`image_id`),
  KEY `fk_image_album` (`image_album_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

请注意，我使用"create_time"来排序图像。

再次，我需要一个查询来获得最后5张图像的create_time排序，但不是由Yii活动记录和普通SQL在同一专辑内。

例如，最后7张图片是:

a.jpg in the album 7
b.jpg in the album 5
c.jpg in the album 7
d.jpg in the album 6
e.jpg in the album 3
f.jpg in the album 4
g.jpg in the album 2
h.jpg in the album 1

我需要查询结果如下所示:

a.jpg in album 7
b.jpg in album 5
d.jpg in album 6
e.jpg in album 3
f.jpg in album 4

不像下面这样:

a.jpg in album 7
b.jpg in album 5
c.jpg in album 7
d.jpg in album 6
e.jpg in album 3

试试这个:

SELECT
  a.album_name,
  i1.image_name
FROM tbl_album a
INNER JOIN tbl_image i1 ON i1.image_album_id = a.album_id
INNER JOIN
(
   SELECT image_album_id, MIN(image_id) image_id
   FROM tbl_image
   GROUP BY image_album_id
) i2  ON i2.image_album_id = i1.image_album_id 
     AND i1.image_id = i2.image_id
ORDER BY a.album_name DESC
LIMIT 5;

JOIN with:

   SELECT image_album_id, MIN(image_id) image_id
   FROM tbl_image
   GROUP BY image_album_id

将确保为每个相册返回第一张图像。因此，每个相册只能得到一张图像，然后LIMIT 5将结果集限制为只有5个相册。

SQL Fiddle Demo

注意: ORDER BY子句将确定LIMIT 5子句将返回哪五张专辑。因此，不要期望专辑按照问题中预期的存储结果返回，因为表中的记录没有特定的顺序。它们被存储为一个集合，您必须在查询中指定ORDER BY子句以按特定顺序获取它们。

Update:如果您正在为每个相册寻找最后创建的图像，请使用MAX(creaet_time)代替，如下所示:

SELECT
  a.album_name,
  i1.image_name,
  date_format(i1.create_time, '%Y-%m-%d') create_time
FROM tbl_album a
INNER JOIN tbl_image i1 ON i1.image_album_id = a.album_id
INNER JOIN
(
   SELECT image_album_id, MAX(create_time) LatestCreateTime
   FROM tbl_image
   GROUP BY image_album_id
) i2  ON i2.image_album_id = i1.image_album_id 
     AND i1.create_time = i2.LatestCreateTime
ORDER BY i1.create_time DESC
LIMIT 5;

更新SQL Fiddle Demo

更新2:对于重复的值，使用DISTINCT关键字，或者您可以使用MAX(image_id)代替，因为image_id是自动增量的，如下所示:

SELECT
  a.album_name,
  i1.image_name,
  date_format(i1.create_time, '%Y-%m-%d') create_time
FROM tbl_album a
INNER JOIN tbl_image i1 ON i1.image_album_id = a.album_id
INNER JOIN
(
   SELECT image_album_id, MAX(image_id) LatestId
   FROM tbl_image
   GROUP BY image_album_id
) i2  ON i2.image_album_id = i1.image_album_id 
     AND i1.image_id = i2.LatestId
ORDER BY i1.create_time DESC
LIMIT 5;

更新SQL Fiddle Demo

我找到了另一个解决我的问题的方法!

SELECT *
FROM tbl_image 
WHERE image_id IN (SELECT MAX(image_id) 
                   FROM tbl_image 
                   GROUP BY image_album_id) 
ORDER BY create_time DESC 
LIMIT 5;

它给了我相同的结果，没有使用内连接，降低性能，你对这个解决方案有什么看法?

SQL小提琴

试试这个，让我知道它返回什么，

select m.image_name,a.album_name
from tbl_album a inner join
tbl_image m on a.album_id=m.image_id
group by m.image_name,a.album_name
order by 2 LIMIT 5