晕!Mysql竟然不支持将同一个表用不同的别名进行关联查询,甚至不能在一条sql语句中两次使用同一个表。
错误提示:
(当然也可以用其他方式变通解决)或者:
错误提示:
#1137 - Can't reopen table: 'mytable'
create
TEMPORARY
table
temp1
as
select * from (
select 1 as id, 50 as ic, ' 2007-10-8 ' as itime, ' a ' as wd union
select 2 as id, 80 as ic, ' 2007-10-9 ' as itime, ' ab ' as wd union
select 3 as id, 50 as ic, ' 2007-11-3 ' as itime, ' abc ' as wd union
select 4 as id, 50 as ic, ' 2007-6-8 ' as itime, ' ad ' as wd union
select 5 as id, 60 as ic, ' 2007-4-9 ' as itime, ' ac ' as wd union
select 6 as id, 80 as ic, ' 2007-12-9 ' as itime, ' aa ' as wd
)a;
create TEMPORARY table temp2 as
select * from (
select 1 as id, 50 as ic, ' 2007-10-8 ' as itime, ' a ' as wd union
select 2 as id, 80 as ic, ' 2007-10-9 ' as itime, ' ab ' as wd union
select 3 as id, 50 as ic, ' 2007-11-3 ' as itime, ' abc ' as wd union
select 4 as id, 50 as ic, ' 2007-6-8 ' as itime, ' ad ' as wd union
select 5 as id, 60 as ic, ' 2007-4-9 ' as itime, ' ac ' as wd union
select 6 as id, 80 as ic, ' 2007-12-9 ' as itime, ' aa ' as wd
)a;
select min (a.id) as id,a.ic,a.itime,a.wd
from temp1 a inner join
(
select ic, max ( cast (itime as date)) as itime from temp2 group by ic
) b on a.ic = b.ic and cast (a.itime as date) = cast (b.itime as date)
group by a.id,a.ic,a.itime,a.wd
order by a.wd
select * from (
select 1 as id, 50 as ic, ' 2007-10-8 ' as itime, ' a ' as wd union
select 2 as id, 80 as ic, ' 2007-10-9 ' as itime, ' ab ' as wd union
select 3 as id, 50 as ic, ' 2007-11-3 ' as itime, ' abc ' as wd union
select 4 as id, 50 as ic, ' 2007-6-8 ' as itime, ' ad ' as wd union
select 5 as id, 60 as ic, ' 2007-4-9 ' as itime, ' ac ' as wd union
select 6 as id, 80 as ic, ' 2007-12-9 ' as itime, ' aa ' as wd
)a;
create TEMPORARY table temp2 as
select * from (
select 1 as id, 50 as ic, ' 2007-10-8 ' as itime, ' a ' as wd union
select 2 as id, 80 as ic, ' 2007-10-9 ' as itime, ' ab ' as wd union
select 3 as id, 50 as ic, ' 2007-11-3 ' as itime, ' abc ' as wd union
select 4 as id, 50 as ic, ' 2007-6-8 ' as itime, ' ad ' as wd union
select 5 as id, 60 as ic, ' 2007-4-9 ' as itime, ' ac ' as wd union
select 6 as id, 80 as ic, ' 2007-12-9 ' as itime, ' aa ' as wd
)a;
select min (a.id) as id,a.ic,a.itime,a.wd
from temp1 a inner join
(
select ic, max ( cast (itime as date)) as itime from temp2 group by ic
) b on a.ic = b.ic and cast (a.itime as date) = cast (b.itime as date)
group by a.id,a.ic,a.itime,a.wd
order by a.wd
(当然也可以用其他方式变通解决)或者:
create
TEMPORARY
table
mytable
as
select * from (
select 1 as id, 50 as ic, ' 2007-10-8 ' as itime, ' a ' as wd union
select 2 as id, 80 as ic, ' 2007-10-9 ' as itime, ' ab ' as wd union
select 3 as id, 50 as ic, ' 2007-11-3 ' as itime, ' abc ' as wd union
select 4 as id, 50 as ic, ' 2007-6-8 ' as itime, ' ad ' as wd union
select 5 as id, 60 as ic, ' 2007-4-9 ' as itime, ' ac ' as wd union
select 6 as id, 80 as ic, ' 2007-12-9 ' as itime, ' aa ' as wd
)a;
create TEMPORARY table temp as
select * from (
select ic, max ( cast (itime as date)) as itime from mytable group by ic
)a;
select min (a.id) as id,a.ic,a.itime,a.wd
from mytable a inner join temp b on a.ic = b.ic and cast (a.itime as date) = cast (b.itime as date)
group by a.id,a.ic,a.itime,a.wd
order by a.wd
select * from (
select 1 as id, 50 as ic, ' 2007-10-8 ' as itime, ' a ' as wd union
select 2 as id, 80 as ic, ' 2007-10-9 ' as itime, ' ab ' as wd union
select 3 as id, 50 as ic, ' 2007-11-3 ' as itime, ' abc ' as wd union
select 4 as id, 50 as ic, ' 2007-6-8 ' as itime, ' ad ' as wd union
select 5 as id, 60 as ic, ' 2007-4-9 ' as itime, ' ac ' as wd union
select 6 as id, 80 as ic, ' 2007-12-9 ' as itime, ' aa ' as wd
)a;
create TEMPORARY table temp as
select * from (
select ic, max ( cast (itime as date)) as itime from mytable group by ic
)a;
select min (a.id) as id,a.ic,a.itime,a.wd
from mytable a inner join temp b on a.ic = b.ic and cast (a.itime as date) = cast (b.itime as date)
group by a.id,a.ic,a.itime,a.wd
order by a.wd