mysql子查询返回由于别名导致的错误

关闭。这个问题需要细节或清晰。它目前不接受答案。
**想改进这个问题吗？**通过编辑这个帖子来添加细节并澄清问题。

9个月前关门了。
改进这个问题
我试图使用子查询从我的数据库中获取数字最低的纬度，限制显示2行（order by lat asc limit 2），同时选择所有返回行并使用order by rand（）将其随机化，我尝试将别名用作t1，但返回错误：“重复的列名'title'”。请帮我指出哪里弄错了。以下是我的疑问：

SELECT *
FROM(SELECT hobber_deal_page.id,hobber_deal_page.merchant_id,hobber_deal_page.category_id,
hobber_deal_page.emirate_id,hobber_deal_page.title,hobber_deal_page.title,
hobber_deal_page.description,hobber_deal_page.emirate,hobber_deal_page.lat,
hobber_deal_page.lng,hobber_deal_page.address,hobber_deal_page.thumbnail_name,
hobber_deal_page.price,hobber_deal_page.created_at, 
AVG(hobber_ratings.ratings) AS ratings 
FROM hobber_deal_page 
LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id 
GROUP BY hobber_deal_page.id 
ORDER BY lat ASC LIMIT 2) t1 
ORDER BY rand()

我在GROUPBY子句中检测到一个失败。为此，查询应如下所示：

SELECT *
FROM(
        SELECT hobber_deal_page.id,
            hobber_deal_page.merchant_id,
            hobber_deal_page.category_id,
            hobber_deal_page.emirate_id,
            hobber_deal_page.title,
            hobber_deal_page.description,
            hobber_deal_page.emirate,
            hobber_deal_page.lat,
            hobber_deal_page.lng,
            hobber_deal_page.address,
            hobber_deal_page.thumbnail_name,
            hobber_deal_page.price,
            hobber_deal_page.created_at,
            AVG(hobber_ratings.ratings) AS ratings
        FROM hobber_deal_page
            LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
        GROUP BY hobber_deal_page.id,
            hobber_deal_page.merchant_id,
            hobber_deal_page.category_id,
            hobber_deal_page.emirate_id,
            hobber_deal_page.title,
            hobber_deal_page.description,
            hobber_deal_page.emirate,
            hobber_deal_page.lat,
            hobber_deal_page.lng,
            hobber_deal_page.address,
            hobber_deal_page.thumbnail_name,
            hobber_deal_page.price,
            hobber_deal_page.created_at
        ORDER BY hobber_deal_page.lat ASC
        LIMIT 2
    ) t1
ORDER BY rand()

如果你把你犯的错误加进去的话，我会更有帮助的。
如何添加一个查询来显示表中的其余行（不包括使用上述查询显示的行）？i、 e将行显示与限制2放在一边。例如，假设表从1到10，上面的查询只能显示3和4，如何显示3和4下面的其余1、2、5、6、7、8、9、10。
你可以做和 UNION （文件）与以前的数据，并避免重复的数据使用 DISTINCT （文件）条款。

SELECT *
FROM(
        SELECT hobber_deal_page.id,
            hobber_deal_page.merchant_id,
            hobber_deal_page.category_id,
            hobber_deal_page.emirate_id,
            hobber_deal_page.title,
            hobber_deal_page.description,
            hobber_deal_page.emirate,
            hobber_deal_page.lat,
            hobber_deal_page.lng,
            hobber_deal_page.address,
            hobber_deal_page.thumbnail_name,
            hobber_deal_page.price,
            hobber_deal_page.created_at,
            AVG(hobber_ratings.ratings) AS ratings
        FROM hobber_deal_page
            LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
        GROUP BY hobber_deal_page.id,
            hobber_deal_page.merchant_id,
            hobber_deal_page.category_id,
            hobber_deal_page.emirate_id,
            hobber_deal_page.title,
            hobber_deal_page.description,
            hobber_deal_page.emirate,
            hobber_deal_page.lat,
            hobber_deal_page.lng,
            hobber_deal_page.address,
            hobber_deal_page.thumbnail_name,
            hobber_deal_page.price,
            hobber_deal_page.created_at
        ORDER BY hobber_deal_page.lat ASC
        LIMIT 2
    ) t1
UNION DISTINCT
SELECT *
FROM(
        SELECT hobber_deal_page.id,
            hobber_deal_page.merchant_id,
            hobber_deal_page.category_id,
            hobber_deal_page.emirate_id,
            hobber_deal_page.title,
            hobber_deal_page.description,
            hobber_deal_page.emirate,
            hobber_deal_page.lat,
            hobber_deal_page.lng,
            hobber_deal_page.address,
            hobber_deal_page.thumbnail_name,
            hobber_deal_page.price,
            hobber_deal_page.created_at,
            AVG(hobber_ratings.ratings) AS ratings
        FROM hobber_deal_page
            LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
        GROUP BY hobber_deal_page.id,
            hobber_deal_page.merchant_id,
            hobber_deal_page.category_id,
            hobber_deal_page.emirate_id,
            hobber_deal_page.title,
            hobber_deal_page.description,
            hobber_deal_page.emirate,
            hobber_deal_page.lat,
            hobber_deal_page.lng,
            hobber_deal_page.address,
            hobber_deal_page.thumbnail_name,
            hobber_deal_page.price,
            hobber_deal_page.created_at
        ORDER BY hobber_deal_page.lat ASC
    ) t2

mysql子查询返回由于别名导致的错误

1条答案

相关问题

热门标签

最新问答