mysql的临时表怎么用呢？以leetcode题《订单最多的客户》为例

Question

今天在leetcode刷mysql练习题的时候遇到了一些问题，原题目如下：

---

---

本人的思路是：
1、先按customer_number分组，用count()函数求出customer_number的订单总数

select customer_number, count(*) as coun from Orders group by customer_number

2、然后在该基础上使用max()函数得到订单总数的最大值，进而得出结果

我的答案如下：

select 
  customer_number
from
  (select customer_number, count(*) as coun from Orders group by customer_number) as t1
where 
  coun=
    (select max(coun) from 
      (select customer_number, count(*) as coun from Orders group by customer_number) as t2)

然后我觉得太冗长了，想简化一下：

select 
    customer_number
from
    (select customer_number, count(*) as coun from Orders group by customer_number) as t1
where coun=(select max(coun) from t1)

然后报错：Table 'test.t1' doesn't exist
如果先执行以下语句，则上面的语句不会报错

DROP TEMPORARY TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 AS
(select customer_number, count(*) as coun from Orders group by customer_number);

于是我再简化了一下，总的代码就是：

DROP TEMPORARY TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 AS
(select customer_number, count(*) as coun from Orders group by customer_number);
select 
    customer_number
from
    t1
where coun=(select max(coun) from t1);

然后这一句又报错：

select 
    customer_number
from
    t1
where coun=(select max(coun) from t1);

报错信息是：can‘t reopen table：’t1‘

所以问题是：

select 
    customer_number
from
    (select customer_number, count(*) as coun from Orders group by customer_number) as t1
where coun=(select max(coun) from t1)

1、这里的t1在where中为什么不能用，再者说临时表怎么用，有什么限制？

2、后面的can‘t reopen table是什么原因？

求解。

Answer 1

直接这样不行么，为什么还用子查询呢
select customer_number, count(*) as coun from Orders group by customer_number ORDER BY count desc LIMIT 1 ;
子查询只限定于可以查询子表中拥有的字段，但是不能再把子表当作表去查询