“未知列”因为子查询中的子查询

发布于 2024-09-12 10:37:54 字数 719 浏览 2 评论 0原文

我需要在子查询中执行子查询，这会导致“‘where 子句’中的未知列‘t1.product_id’”。在我的示例中，它位于第 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

这个问题继续LIMIT忽略在查询与GROUP_CONCAT

原文

I need to do subquery in subquery what causes "Unknown column 't1.product_id' in 'where clause'". It's on line 7. in my example. How to solve this problem?

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

This question continues from LIMIT ignored in query with GROUP_CONCAT

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笨笨の傻瓜 2024-09-19 10:37:54

使用派生表/内联视图和表别名：

  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

Use a derived table/inline view, and table aliases:

  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

回复收藏 0 原文

萌面超妹 2024-09-19 10:37:54

我发现在超过 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

I have found it more comfortable to use variables in Queries with More than 2 Selects Deep.

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

回复收藏 0 原文

~没有更多了~