同一个表统计数据,由于表字段直接没有建立关系,处理方式是建立临时表,再建立关系。

例子:

 select * from ( select addcode,SUBSTRING(addcode,1,7)as child,AddFullName from [Address] where AddLevel=2) as parent_table
 left join
 (select SUBSTRING(addcode,1,7)as child, COUNT(SUBSTRING(addcode,1,7)) as childCount from [Address] where AddLevel=3
 group by SUBSTRING(addcode,1,7)) as child_table
 on parent_table.child=child_table.child order by parent_table.AddCode