mysql - SQL 计数查询的性能改进

Question

我将使用什么类型的 sql 查询来转换以下内容；

|  ID  |  SERIAL  |  LCN  | INITLCN |
|------|----------|-------|---------|
|  1   |    A     |  A1   |         |
|  2   |    B     |  A2   |         |
|  3   |    C     |  A3   |   A1    |
|  4   |    D     |  A4   |   A2    |
|  5   |    E     |  A5   |   A1    |
|------|----------|-------|---------|

变成类似这样的结果；

|  ID  |  COUNT  |
|------|---------|
|  1   |    2    |
|  2   |    1    |
|------|---------|

利用我的低 SQL 技能，我设法编写了以下查询，但是它非常慢；

select
  a.id,
  count (b.id) as parent
from assets a 
left join assets b
  ON (a.lcn = b.initlcn)
group by a.id
order by a.id;

Answer 1

select
t1.ID,
t1.LCN,
COUNT(*)
from
Table1 t1
INNER JOIN Table1 t2 ON t1.LCN = t2.INITLCN
GROUP BY t1.LCN

看到它在 sqlfiddle 中实时运行.