mysql - 我在查询 SQL (UNION ALL) 中哪里出错了？

Question

我尝试使用UNION ALL，但出现错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY Product_id' at line 13

如何为 UNION 表和结果 GROUP BY 设置正确的别名？

示例:sqlfiddle

Answer 1

这是正确的查询结构，UNION/ALL 不需要别名，但派生表需要。

SELECT
  Product_id,
  sum(Total) as sumOfTotal
FROM ((SELECT
  b.Products_idProducts AS Product_id,
  COUNT(b.Products_idProducts) AS Total
FROM products_has_colors a
RIGHT JOIN products_has_colors b
  ON a.Colors_idColors = b.Colors_idColors
  AND b.Products_idProducts <> a.Products_idProducts
WHERE a.Products_idProducts = 1
GROUP BY b.Products_idProducts)

UNION ALL

(SELECT
  b.Products_idProducts AS Product_id,
  COUNT(b.Products_idProducts) AS Total
FROM Products_has_ProductComposition a
RIGHT JOIN Products_has_ProductComposition b
  ON (a.CompositionId = b.CompositionId
  AND b.Products_idProducts <> a.Products_idProducts)
WHERE a.Products_idProducts = 1
GROUP BY b.Products_idProducts)) mytable
GROUP BY Product_id;