我有两个表,products
和image
:
SELECT
products.id,
products.name,
MAX(image.angle) AS image_angle,
image.url as image_url,
image.width as image_width,
image.height as image_height
FROM products
JOIN `image` ON (`products`.`id` = `image`.`product_id`)
WHERE image.width = 100
aND image.height = 200
在图像中有不同尺寸的图像存储
在product
表中有一个称为image_url
的列,在image
表中有url
列。T上面的查询给了我正确的角度最大值(来自image
表),但image.url
的值不正确。
结果可能是:
(
[id] => 308
[name] => Volvo240
[image__angle] => 4
[url] => /image/308/1_100_200.png
[image__width] => 100
[image__height] => 200
)
url
从product
表中获取值,但我希望url
从image
表中获取(在这种情况下):/image/308/**4**_100_200.png
我不知道这是否重要,但我正在使用CodeIgniter和ActiveRecord来创建我的查询。
如果产品表没有很多行(下面10K),可以使用以下代码:
SELECT
products.id,
products.name,
(SELECT image.angle
FROM image
WHERE `image`.`product_id` = `products`.`id`
AND `image`.`width` = 100
AND `image`.`height` = 200
ORDER BY image.angle DESC
LIMIT 0, 1) AS image_angle,
(SELECT image.url
FROM image
WHERE `image`.`product_id` = `products`.`id`
AND `image`.`width` = 100
AND `image`.`height` = 200
ORDER BY image.angle DESC
LIMIT 0, 1) AS image_url,
100 AS image_width,
200 AS image_height
FROM
products
如果产品表有大量的行,请使用以下代码以获得更好的性能:
SELECT
products.id,
products.name,
image.angle AS image_angle,
image.url AS image_url,
image.width AS image_width,
iamge.height AS image_height
FROM
products
JOIN
(SELECT product_id, max(angle) AS max_angle
FROM image
WHERE width = 100 AND height = 200
GROUP BY product_id) AS t1 ON (t1.product_id = product.id)
JOIN
image ON (image.product_id = t1.product_id AND image.angle = t1.max_angle)
WHERE
image.width = 100 AND
image.height = 200
但是上面的代码要求您的图像表的记录在(product_id,angle,width,height)上是唯一的,否则您可能会为一个产品获得多行。
使用如下子查询:
SELECT
products.id,
products.name,
(select
MAX(image.angle)
from
products
WHERE
image.width = 100 aND image.height = 200) AS image_angle,
image.url as image_url,
image.width as image_width,
image.height as image_height
FROM
products
JOIN `image` ON (`products`.`id` = `image`.`product_id`)
WHERE
image.width = 100 aND image.height = 200
您可以从子查询中删除where条件(根据需要)。
您需要将GROUP BY添加到product.id或image.id同时在角度上添加ORDER BY。
SELECT
products.id,
products.name,
products.category_id,
image.angle AS image_angle,
image.url as image_url,
image.width as image_width,
image.height as image_height
FROM products
WHERE image.width = 100
aND image.height = 200
GROUP BY `products.id`
ORDER BY `image.angle` DESC