MySQL 带有别名的多个 UNION

Question

我已经看了几个小时了，但找不到错误:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 't2 UNION (SELECT a.item_id ' at line 34'

我尝试了括号和别名的不同变体，我尝试将 SELECT DISTINCT 与 UNION ALL 一起使用，我已经验证了 ? 参数匹配...我缺少什么？

SELECT COUNT(*)
FROM (SELECT    a.item_id
      FROM      catalog_items AS a
      JOIN      catalog_franchises AS b ON a.game_id = b.franchise_id
      JOIN      catalog_franchises AS c ON a.manufacturer_id = c.franchise_id
      LEFT JOIN catalog_franchises AS d ON a.exclusive_id = d.franchise_id
      JOIN      catalog_item_categories AS e ON a.item_id = e.item_id
                AND e.valid = TRUE
      JOIN      catalog_categories AS f ON e.cat_id = f.cat_id AND f.parent_id = ?
                AND f.cat_id IN (?,?,?) AND f.valid = TRUE
      WHERE     d.franchise_id = ?
                AND a.valid = TRUE
                AND b.valid = TRUE
                AND c.valid = TRUE
                AND (d.valid = TRUE OR d.title IS NULL)
                AND a.region_id = ?
                AND a.quantity IS NOT NULL) t1
      UNION
     (SELECT    a.item_id
      FROM      catalog_items AS a
      JOIN      catalog_franchises AS b ON a.game_id = b.franchise_id
      JOIN      catalog_franchises AS c ON a.manufacturer_id = c.franchise_id
      LEFT JOIN catalog_franchises AS d ON a.exclusive_id = d.franchise_id
      JOIN      catalog_item_categories AS e ON a.item_id = e.item_id
                AND e.valid = TRUE
      JOIN      catalog_categories AS f ON e.cat_id = f.cat_id AND f.parent_id = ?
                AND f.cat_id IN (?,?) AND f.valid = TRUE
      WHERE     d.franchise_id = ?
                AND a.valid = TRUE
                AND b.valid = TRUE
                AND c.valid = TRUE
                AND (d.valid = TRUE OR d.title IS NULL)
                AND a.region_id = ?
                AND a.quantity IS NOT NULL) t2
      UNION
     (SELECT    a.item_id
      FROM      catalog_items AS a
      JOIN      catalog_franchises AS b ON a.game_id = b.franchise_id
      JOIN      catalog_franchises AS c ON a.manufacturer_id = c.franchise_id
      LEFT JOIN catalog_franchises AS d ON a.exclusive_id = d.franchise_id
      JOIN      catalog_editions AS e ON a.edition_id = e.edition_id AND e.edition_id IN (?,?,?) AND e.valid = TRUE
      WHERE     d.franchise_id = ?
                AND a.valid = TRUE
                AND b.valid = TRUE
                AND c.valid = TRUE
                AND (d.valid = TRUE OR d.title IS NULL)
                AND a.region_id = ?
                AND a.quantity IS NOT NULL) t3

Answer 1

它应该是这样的

select * from (
select ..
union
select...
) as x  -- you need alias here

你做错的是

select ... from
(
select ...
) as t1
union 
select ... -- then this prompted an error because the derived table just ended at the ')', anything after the 't1' is irrelevant