mysql - SQL - Union 和 Order BY 的错误使用

Question

SELECT Count(STATUS),
   STATUS,
   ROUND(Count(STATUS) * 100 /
           (SELECT Count(*)
            FROM call_view
            WHERE (create_month = Month(Now() - INTERVAL 1 MONTH))
              AND (create_year = Year(Now() - INTERVAL 1 MONTH))
              AND customer_company_name = "Company" ), 1) AS Percentage
FROM call_view
WHERE (create_month = Month(Now() - INTERVAL 1 MONTH))
AND (create_year = Year(Now() - INTERVAL 1 MONTH))
AND customer_company_name = "Company"
GROUP BY STATUS
ORDER BY COUNT(CALL_ID) DESC LIMIT 6
UNION
SELECT Sum(category_count),
   'Other',
   sum(percentage)
FROM
( SELECT Count(CATEGORY) AS category_count,
       COUNT(CALL_ID),
       CATEGORY,
       ROUND(Count(CATEGORY) * 100 /
               (SELECT Count(*)
                FROM call_view
                WHERE (create_month = Month(Now() - INTERVAL 1 MONTH))
                  AND (create_year = Year(Now() - INTERVAL 1 MONTH))
                  AND customer_company_name = "Company" ), 1) 
AS Percentage FROM call_view
WHERE (create_month = Month(Now() - INTERVAL 1 MONTH))
  AND (create_year = Year(Now() - INTERVAL 1 MONTH))
  AND customer_company_name = "Company"
GROUP BY CATEGORY
ORDER BY COUNT(CALL_ID) DESC LIMIT 6,
                                  1525125215) AS original_query

我收到错误“UNION 和 ORDER BY 的使用不正确”。

我正在使用此查询作为工具，并且需要它才能工作。

我知道有一个解决方案，您可以在两个查询的开头和结尾使用 Parathesis()，但我不能将其用于我的工具。

这就是为什么我需要这个查询的其他解决方案，而不是使用 () 解决方案

还有其他解决办法吗？

非常感谢您的帮助...两天以来一直遇到此问题...

干杯

Answer 1

试试这个:

(
    SELECT Count(STATUS),  
    STATUS, 
    ROUND(Count(STATUS) * 100 / (SELECT Count(*) 
                                  FROM   call_view 
                                  WHERE 
    ( create_month = Month(Now() - 
                    INTERVAL 1 month) ) 
     AND ( create_year = Year( 
         Now() - INTERVAL 1 month) ) 
     AND customer_company_name = "Company"
       ), 1) AS Percentage
    FROM   call_view 
    WHERE  ( create_month = Month(Now() - INTERVAL 1 month) ) 
    AND ( create_year = Year(Now() - INTERVAL 1 month) ) 
    AND customer_company_name = "Company"
    GROUP BY STATUS
    ORDER BY COUNT(CALL_ID) DESC
    limit 6
)
union
(
    Select Sum(category_count), 'Other', sum(percentage) 
    from (
    SELECT Count(CATEGORY) as category_count,  COUNT(CALL_ID), CATEGORY,

    ROUND(Count(CATEGORY) * 100 / (SELECT Count(*) 
                              FROM   call_view 
                              WHERE 
      ( create_month = Month(Now() - 
                INTERVAL 1 month) ) 
    AND ( create_year = Year( 
      Now() - INTERVAL 1 month) ) 
    AND customer_company_name ="Company"
    ), 1) AS Percentage
    FROM   call_view 
    WHERE  ( create_month = Month(Now() - INTERVAL 1 month) ) 
    AND ( create_year = Year(Now() - INTERVAL 1 month) ) 
       AND customer_company_name = "Company"
       GROUP BY CATEGORY
      ORDER BY COUNT(CALL_ID) DESC
    limit 6, 1525125215) as original_query
)