Mysql

MYSQL 錯誤：UNION 和 ORDER BY 的使用不正確？

  • August 2, 2020

我試圖隨機化我的第一個查詢，但遇到了錯誤“UNION 和 ORDER BY 的使用不正確”請在我的查詢下方找到：

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

我不確定我在哪裡弄錯了。在不添加“ORDER BY RAND()”的情況下，我的查詢執行良好，但我需要第一個查詢來隨機化。我需要一個關於如何做到這一點的指南。

您可以簡單地將 Select … ORDER BY RAND() 放在括號中，以便 MysSQL 知道如何處理它

(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() LIMIT 18446744073709551615)
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)

基本上是這樣的，但正如我提到的沒有測試數據的評論，不清楚，如果這是他的解決方案

(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() LIMIT 18446744073709551615)
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
       WHERE  hobber_deal_page.id NOT IN (SELECT hobber_deal_page.id
   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)
   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)

引用自：https://dba.stackexchange.com/questions/273043

相關問答

Mysql

MySQL查詢：檢測另一個表中已刪除的行並顯示在結果表中

  • August 31, 2022
檢視問答
Mysql

使用多個條件從兩個表中選擇不同的行

  • March 21, 2022
檢視問答
Mysql

MySQL 多個子查詢 - 排序緩慢

  • November 6, 2021
檢視問答
Mysql

使用 Pagination MYSQL 從以前的記錄計算借方貸方餘額

  • February 22, 2021
檢視問答
Mysql

如何刪除特定行

  • November 4, 2020
檢視問答
Mysql

銳化時查詢未找到值

  • July 6, 2020
檢視問答