Mysql竟然不支持同一个表的关联查询

cencenhai

晕！Mysql竟然不支持将同一个表用不同的别名进行关联查询，甚至不能在一条sql语句中两次使用同一个表。
错误提示： #1137 - Can't reopen table: 'mytable' createTEMPORARYtabletemp1as
select*from(
select1asid,50asic,'2007-10-8'asitime,'a'aswdunion
select2asid,80asic,'2007-10-9'asitime,'ab'aswdunion
select3asid,50asic,'2007-11-3'asitime,'abc'aswdunion
select4asid,50asic,'2007-6-8'asitime,'ad'aswdunion
select5asid,60asic,'2007-4-9'asitime,'ac'aswdunion
select6asid,80asic,'2007-12-9'asitime,'aa'aswd
)a;

createTEMPORARYtabletemp2as
select*from(
select1asid,50asic,'2007-10-8'asitime,'a'aswdunion
select2asid,80asic,'2007-10-9'asitime,'ab'aswdunion
select3asid,50asic,'2007-11-3'asitime,'abc'aswdunion
select4asid,50asic,'2007-6-8'asitime,'ad'aswdunion
select5asid,60asic,'2007-4-9'asitime,'ac'aswdunion
select6asid,80asic,'2007-12-9'asitime,'aa'aswd
)a;

selectmin(a.id)asid,a.ic,a.itime,a.wd
fromtemp1ainnerjoin
(
selectic,max(cast(itimeasdate))asitimefromtemp2groupbyic
)bona.ic=b.icandcast(a.itimeasdate)=cast(b.itimeasdate)
groupbya.id,a.ic,a.itime,a.wd
orderbya.wd


(当然也可以用其他方式变通解决)或者：
createTEMPORARYtablemytableas
select*from(
select1asid,50asic,'2007-10-8'asitime,'a'aswdunion
select2asid,80asic,'2007-10-9'asitime,'ab'aswdunion
select3asid,50asic,'2007-11-3'asitime,'abc'aswdunion
select4asid,50asic,'2007-6-8'asitime,'ad'aswdunion
select5asid,60asic,'2007-4-9'asitime,'ac'aswdunion
select6asid,80asic,'2007-12-9'asitime,'aa'aswd
)a;

createTEMPORARYtabletempas
select*from(
selectic,max(cast(itimeasdate))asitimefrommytablegroupbyic
)a;

selectmin(a.id)asid,a.ic,a.itime,a.wd
frommytableainnerjointempbona.ic=b.icandcast(a.itimeasdate)=cast(b.itimeasdate)
groupbya.id,a.ic,a.itime,a.wd
orderbya.wd