本文探讨了MySQL在自关联查询方面的限制，即无法在同一SQL语句中多次引用同一张表，并给出了通过创建临时表来规避这一限制的方法。
晕！Mysql竟然不支持将同一个表用不同的别名进行关联查询，甚至不能在一条sql语句中两次使用同一个表。
错误提示：
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
temp1a
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
mytablea
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
错误提示：
#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
temp1a
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
mytablea
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
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
