由于子查询中的子查询“未知列”
我需要在子查询中执行子查询是什么导致“未知列't1.product_id'在'where子句'”中。在我的例子中,它在第7行。如何解决这个问题呢?由于子查询中的子查询“未知列”
SELECT *,product_id id,
(SELECT GROUP_CONCAT (value ORDER By `order` ASC SEPARATOR ', ')
FROM (
SELECT `order`,value
FROM slud_data
LEFT JOIN slud_types ON slud_types.type_id=slud_data.type_id
WHERE slud_data.product_id = t1.product_id
AND value!='' AND display=0
LIMIT 3
) tmp) text
FROM slud_products t1
WHERE
now() < DATE_ADD(date,INTERVAL +ttl DAY) AND activated=1
ORDER BY t1.date DESC
使用派生表/内嵌视图继续,并且表的别名:
SELECT product_id AS id,
GROUP_CONCAT (y.value ORDER BY y.`order`)
FROM slud_products t1
JOIN (SELECT sd.product_id,
sd.value,
sd.`order`
FROM SLUD_DATA sd
LEFT JOIN slud_types ON slud_types.type_id = slud_data.type_id
WHERE value! = ''
AND display = 0) y ON y.product_id = t1.product_id
AND y.order <= 3
WHERE now() < DATE_ADD(date,INTERVAL +ttl DAY)
AND activated = 1
GROUP BY product_id
ORDER BY t1.date DESC
你好,仍然想知道如何处理来自原始查询的“LIMIT 3”子句?如果'order'不是sequental字段或不是基于零? – Dfr 2011-05-27 07:58:08
@Dfr:'AND y.order 2011-05-27 14:50:06
我发现它更舒适的使用在查询中的变量有2个以上选择深度。
SELECT *,@product := product_id id,
(SELECT GROUP_CONCAT (value ORDER By `order` ASC SEPARATOR ', ')
FROM (
SELECT `order`,value
FROM slud_data
LEFT JOIN slud_types ON slud_types.type_id=slud_data.type_id
WHERE slud_data.product_id = @product
AND value!='' AND display=0
LIMIT 3
) tmp) text
FROM slud_products t1
WHERE
now() < DATE_ADD(date,INTERVAL +ttl DAY) AND activated=1
ORDER BY t1.date DESC
这不适用于所有版本的MySQL。 – 2014-06-02 20:31:46
在没有ORDER BY子句的情况下,LIMIT依赖表索引的底层结构,这对于某些人来说是有点不可预知的。 – Strawberry 2013-04-05 16:58:10