mysql - 连接多个查询错误

Question

第一个查询

SELECT a.*,
       ifnull(cnt_all,0) total_drivers,
       ifnull(cnt_active,0) active_drivers,
       ifnull(cnt_idle,0) idle_drivers
FROM ta_agent a
LEFT JOIN
  (SELECT agent_id,
          count(*) cnt_all
   FROM ta_drivers
   GROUP BY agent_id) cnt ON a.agent_id=cnt.agent_id
LEFT JOIN
  (SELECT agent_id,
          count(*) cnt_idle
   FROM ta_drivers
   WHERE last_viewed=0
   GROUP BY agent_id) idle ON a.agent_id=idle.agent_id
LEFT JOIN
  (SELECT agent_id,
          count(*) cnt_active
   FROM ta_drivers
   WHERE last_viewed=1
   GROUP BY agent_id) active ON a.agent_id=active.agent_id

第二个查询

SELECT FROM_UNIXTIME(date_of_registration, '%Y-%m-%d %H:%i:%s') AS user_registeredon
FROM ta_agent a,
     ta_subscription s
WHERE s.agent_id = a.agent_id

当我单独运行时，这两个查询工作正常。我想加入这两个查询。我尝试连接这两个查询，但收到此错误 每个派生表必须有自己的别名

我已经尝试过了

select a.*, ifnull(cnt_all,0) total_drivers,ifnull(cnt_active,0) active_drivers, ifnull(cnt_idle,0) idle_drivers
    from ta_agent a left join (select agent_id, count(*) cnt_all
                       from ta_drivers
                       group by agent_id) cnt on a.agent_id=cnt.agent_id

    left join (select agent_id, count(*) cnt_idle
                       from ta_drivers
                       where last_viewed=0
                       group by agent_id) idle on a.agent_id=idle.agent_id

    left join (select agent_id, count(*) cnt_active
                       from ta_drivers
                       where last_viewed=1
                       group by agent_id) active on a.agent_id=active.agent_id

    left join(SELECT FROM_UNIXTIME( date_of_registration, '%Y-%m-%d %H:%i:%s' ) AS user_registeredon FROM ta_subscription WHERE agent_id = a.agent_id)

我认为这不是获得结果的正确方法..

Answer 1

请提及您想如何加入这两个查询..正在寻找类似下面的内容:

SELECT *
FROM
  (SELECT a.*,
          ifnull(cnt_all,0) total_drivers,
          ifnull(cnt_active,0) active_drivers,
          ifnull(cnt_idle,0) idle_drivers
   FROM ta_agent a
   LEFT JOIN
     (SELECT agent_id,
             count(*) cnt_all
      FROM ta_drivers
      GROUP BY agent_id) cnt ON a.agent_id=cnt.agent_id
   LEFT JOIN
     (SELECT agent_id,
             count(*) cnt_idle
      FROM ta_drivers
      WHERE last_viewed=0
      GROUP BY agent_id) idle ON a.agent_id=idle.agent_id
   LEFT JOIN
     (SELECT agent_id,
             count(*) cnt_active
      FROM ta_drivers
      WHERE last_viewed=1
      GROUP BY agent_id) active ON a.agent_id=active.agent_id)tempaliasA
LEFT JOIN
  (SELECT FROM_UNIXTIME(date_of_registration, '%Y-%m-%d %H:%i:%s') AS user_registeredon,
          agent_id
   FROM ta_agent a,
        ta_subscription s
   WHERE s.agent_id = a.agent_id)tempaliasB ON tempaliasA.agent_id = tempaliasB.agent_id