sql - 查询宽度和高度，在同一个查询中一个记录大于另一个记录？

Question

我正在尝试创建一个查询，该查询将从具有以下要求的表中提取结果:

1) Query database table for 5 records.
2) At least one record with image height greater than image width.
3) At least one record with image width greater than image height.
4) Results must be ordered by newest records first (time)

这将如何完成？谢谢!

CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `image` char(100) NOT NULL DEFAULT '',
  `image_width` smallint(4) unsigned NOT NULL DEFAULT '0',
  `image_height` smallint(4) unsigned NOT NULL DEFAULT '0',
  `time` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)

编辑:添加了时间字段和时间顺序要求。记录必须按时间字段排序(示例中使用了任意 int 字段)。

Answer 1

SELECT * FROM images WHERE id IN (
     SELECT id FROM images WHERE image_width > image_height ORDER BY RAND() LIMIT 1
     UNION
     SELECT id FROM images WHERE image_height > image_width ORDER BY RAND() LIMIT 1
     UNION
     SELECT id FROM images ORDER BY RAND() LIMIT 3
)