Query for width and height, a record with each greater than the other in the same query?
I am trying to create a single query that will pull results from a table with the following requirements:
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)
How would this be done? Thanks!
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`)
)
E开发者_C百科DIT: Added time field and time order requirement. Records must be ordered by the time field (used an arbitrary int field for the example).
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
)
To expand on the other union attempts... The inner most query should return 7 records for possibility that even with random, you could STILL get the WIDTH and/or HEIGHT image. So, taking that inner most query and sorting by the extra column I added of "Required" = 1 or 2 will put the required ones first and secondary ones after. Then, the OUTER query will select DISTINCT elements from the inner query which SHOULD begin with natural order of inner query result of 1 Required, 1 Required, 2 Required, 2 Required, 2 Required, 2 Required, 2 Required. By doing distinct on the ID, if the first "2 required" is the same ID as width or height, it will immediately be ignored leaving the outer natural result set to 6 records... So the outer most LIMIT will then force truncating it down to the final 5 you were hoping for.
select images.*
from images,
( select distinct id
from ( SELECT QryUnions.id,
QryUnions.required
FROM ((select imgW.id,
1 Required,
rand() rand1
from images imgW
where imgW.image_width > imgW.image_height
group by 1
order by 3
limit 1)
union all
(select imgH.id,
1 Required,
rand() rand1
from images imgH
where imgH.image_height > imgH.image_width
group by 1
order by 3
limit 1)
union all
(select imgR.id,
2 Required,
rand() rand1
from images imgR
group by 1
order by 3
limit 5)) QryUnions
order By
QryUnions.Required ) PreQualify
order by
Required
limit 5 ) Final5
where
images.id = Final5.id
SELECT * FROM images WHERE image_width > image_height LIMIT 1
UNION
SELECT * FROM images WHERE image_height > image_width LIMIT 1
UNION
(SELECT * FROM images LIMIT 3);
To avoid row duplication you can use the following:
SELECT DISTINCT * FROM (
SELECT * FROM images WHERE image_width > image_height LIMIT 1
UNION
SELECT * FROM images WHERE image_height > image_width LIMIT 1
UNION
(SELECT * FROM images LIMIT 3)
) AS i;
SELECT DISTINCT id FROM (
SELECT *, 'A' AS ord FROM images WHERE image_width > image_height LIMIT 1
UNION
SELECT *, 'B' AS ord FROM images WHERE image_height > image_width LIMIT 1
UNION
(SELECT *, 'C' AS ord FROM images)
) AS i ORDER BY ord LIMIT 5;
If you want to retrieve the full rows you can do the following instead:
SELECT * FROM images WHERE id IN (
SELECT id FROM (
SELECT *, 'A' AS ord FROM images WHERE image_width > image_height LIMIT 1
UNION
SELECT *, 'B' AS ord FROM images WHERE image_height > image_width LIMIT 1
UNION
(SELECT *, 'C' AS ord FROM images LIMIT 7)
) AS i ORDER BY ord
) LIMIT 5;
精彩评论