How to left join intermediate result table after filter ta(subquery)? +---------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                           |
+---------+----------------------------------------------------------------------------------------------------------------------------------------+
| stuInfo | CREATE TABLE `stuInfo` (
  `stu_name` varchar(20) DEFAULT NULL,
  `stu_no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------+


+----------+--------+
| stu_name | stu_no |
+----------+--------+
| Abcd     |      1 |
| Acmn     |      1 |
| Borne    |      1 |
| Bence    |      1 |
| Lucy     |      1 |
| Jone     |      2 |
| Lucy     |      2 |
| Lily     |      2 |
+----------+--------+


I want to get the number of (A*, 1), (B*, 1) as below

+----------+--------+
| A        | B      |
+----------+--------+
| 2        |      2 |
+----------+--------+


I consider of using left join.

select count(ta.stu_name) as A, count(tb.stu_name) as B 
from (
    select stu_name, stu_no 
    from stuInfo 
    where stu_no=1
) filtered_table 
left join filtered_table ta 
    on stu_name like 'A%' 
left join filtered_table tb 
    on stu_name like 'B%';


However this doesn't work.
What I want to know is how can I first base on  some condition (my example, the condition is 'stu_no=1'), and then use the filtered table to do left join.
 I think the get the filtered table first can improve the performance as there are multiple left join tables(same table).

The worked sql is as below:

select count(ta.stu_name) as A, count(tb.stu_name) as B 
from ( 
    select stu_name, stu_no 
    from stuInfo 
    where stu_no = 1 
      and stu_name like 'A%'
) ta left join (
    select stu_name, stu_no 
    from stuInfo 
    where stu_no = 1 
      and stu_name like 'B%'
) tb on 1=1;
I'm not sure I understand your problem, but judging from your sample data you can use a conditional count:

SELECT COUNT(CASE WHEN stu_name like 'A%' then 1 end) AS A
     , COUNT(CASE WHEN stu_name like 'B%' then 1 end) AS B
FROM stuInfo
WHERE stu_no = 1;


CASE WHEN stu_name like 'A%' then 1 end evaluates to 1 if stu_name starts with A and null otherwise. COUNT counts all non-null expressions.