无法执行联合，正在获取#1248 -每个派生表都必须有自己的别名错误

Question

我有以下两个查询

select 
    t . *, events.event_time as last_time
from
    events,
(
    (
        select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 1
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    ) 
    union 
    (
    select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 2
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    ) 
    union 
    (
    select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 5
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    ) 
    union 
    (
    select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 9
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    ) 
    union 
    (
    select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 10
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    )

)as t where events.global_id = t1.last

另一个是：

SELECT 
    post_stream.type,
    post_stream.ref_global_id as target_post,
    post_stream.user_id as on_whose_post,
    post_stream.user_id as shooter_ids,
    post_stream.ref_global_id as shooted_what,
    post_stream.ref_global_id as last,
    'stream' as flag,
    events.event_time as last_time
FROM
    post_stream,
    events,
    relation_table
WHERE
    events.global_id = post_stream.ref_global_id
        and post_stream.type IN (2 , 3, 7, 8)
        AND post_stream.user_id = relation_table.user2
        AND relation_table.user1 = 192

现在我需要在两个查询上执行连接以获得组合结果，但它给每个派生表都必须有自己的别名错误，我应该为派生表放置一个别名这两个查询在单独运行时没有错误。

Answer 1

第一个查询的格式约为116行，包含一个5向UNION子查询。除了WHERE子句中的一个值之外，这5个子查询似乎是相同的。这种重写极大地简化了SQL以：

SELECT t.type, t.target_post, t.on_whose_post, t.shooter_ids, t.shooted_what,
       t.last, t.flag, events.event_time AS last_time
  FROM events JOIN
       (SELECT bonding.type,
               bonding.global_id2 AS target_post,
               bonding.target_id AS on_whose_post,
               GROUP_CONCAT(bonding.shooter_id) AS shooter_ids,
               GROUP_CONCAT(bonding.what_global_id) AS shooted_what,
               MAX(bonding.what_global_id) AS last,
               'bonding' AS flag
          FROM bonding
         WHERE bonding.TYPE IN (1, 2, 5, 9, 10)
           AND bonding.shooter_id IN (SELECT user2 FROM relation_table WHERE user1 = 192)
         GROUP BY bonding.global_id2
        ) AS t
     ON events.global_id = t1.last

这将更容易与第二个查询相结合。在进一步的修订中，我可能会删除bonding.前缀，因为主子查询中唯一的表是bonding。

第二个查询也应该使用连接表示法重写：

SELECT p.type          AS type,
       p.ref_global_id AS target_post,
       p.user_id       AS on_whose_post,
       p.user_id       AS shooter_ids,
       p.ref_global_id AS shooted_what,
       p.ref_global_id AS last,
       'stream'        AS flag,
       e.event_time    AS last_time
  FROM post_stream    AS p
  JOIN events         AS e ON e.global_id = p.ref_global_id
  JOIN relation_table AS r ON p.user_id = r.user2
 WHERE r.user1 = 192
   AND post_stream.type IN (2 , 3, 7, 8)

问题：

1. 是否确定on_whose_post和shooter_ids应来自同一列？
2. 是否确定shooted_what和last应来自同一列？

这样做可能有合理的(也不是太牵强的)理由--但不是一目了然的。

不幸的是，我们还没有被告知如何将上面第一个查询的数据与第二个查询连接起来。似乎有相当多的列是相同的；只有操作员才能确定需要什么。