[高分！急！]请问如何在 MySQL 里同时使用两条互斥的语句？

[Kuye2002yx]

我首先通过

SELECT id, name, region, realname, votes FROM players ORDER BY votes, name LIMIT 5;

将投票前五名列出来了，然后需要将剩下的人，排除掉刚才列出的前五名，然后以 ORDER BY name 的方式列出来，该怎么做呢？

[wwwwb]

select * from (
SELECT id, name, region, realname, votes FROM players ORDER BY votes, name LIMIT 5)

union

select a.* from players a left join
(SELECT id, name, region, realname, votes FROM players ORDER BY votes, name LIMIT 5) b
on a.id=b.id and a.name=b.name and a.region=b.region and a.realname=b.realname
and a.votes=b.votes
where isnull(b.id)

[懒得去死]

按照你的思路最直接的SQL 语句，没有做优化。

select a.id, a.name, a.region,  a.realname, a.votes
from players as a left join 
(
SELECT id, name, region,  realname, votes FROM players ORDER BY votes, name LIMIT 5
) as b
on (a.id = b.id) where b.id is null;

[wwwwb]

排除掉刚才列出的前五名，然后以 ORDER BY name 的方式列出来

select a.* from players a left join
(SELECT id, name, region, realname, votes FROM players ORDER BY votes, name LIMIT 5) b
on a.id=b.id and a.name=b.name and a.region=b.region and a.realname=b.realname
and a.votes=b.votes
where isnull(b.id) order by a.name

[liuyann]

排除掉刚才列出的前五名，然后以 ORDER BY name 的方式列出来，该怎么做呢？

SELECT id, name, region,  realname, votes FROM players ORDER BY votes, name LIMIT 5,999999; 

==== 思想重于技巧 ====

reply via CSDN viewer

[Kuye2002yx]

[Quote=引用 1 楼 wwwwb 的回复:]
select * from (
SELECT id, name, region, realname, votes FROM players ORDER BY votes, name LIMIT 5)

union

select a.* from players a left join
(SELECT id, name, region, realname, votes FROM players ORDER BY votes, name LIMIT 5) b
on a.id=b.id and a.name=b.name and a.region=b.region and a.realname=b.realname
and a.votes=b.votes
where isnull(b.id)
[/Quote]

[Quote=引用 2 楼 yueliangdao0608 的回复:]
按照你的思路最直接的SQL 语句，没有做优化。
SQL codeselecta.id, a.name, a.region, a.realname, a.votesfromplayersasaleftjoin(SELECTid, name, region, realname, votesFROMplayersORDERBYvotes, name LIMIT5)asbon(a.id=b.id)whereb.idisnull;
[/Quote]

这两条符合条件了，但是我的 MySQL 版本好像太低了，直接给我报了一个

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id, name, region, realname, votes FROM players

错误。

MySQL 4.0.12 有什么办法能达到相同的效果吗?

[Kuye2002yx]

[Quote=引用 4 楼 liuyann 的回复:]
排除掉刚才列出的前五名，然后以 ORDER BY name 的方式列出来，该怎么做呢？

SQL codeSELECTid, name, region, realname, votesFROMplayersORDERBYvotes, name LIMIT5,999999;
====思想重于技巧====

reply via CSDN viewer
[/Quote]

您的直接做到了排除前五名，但没有做到把剩下的以 name 进行排序列出。

[wwwwb]

select * from (
SELECT id, name, region, realname, votes FROM players ORDER BY votes, name LIMIT 5,999999)
order by name

试试

[wwwwb]

如果不支持上述语句，只有将
SELECT id, name, region, realname, votes FROM players ORDER BY votes, name LIMIT 5,999999
存为临时表，再SELECT

[Kuye2002yx]

[Quote=引用 8 楼 wwwwb 的回复:]
如果不支持上述语句，只有将
SELECT id, name, region,  realname, votes FROM players ORDER BY votes, name LIMIT 5,999999
存为临时表，再SELECT
[/Quote]

这个确实也不行，给我报了 error in your SQL syntax，但是存成临时表并不完善，一是要考虑到并发的问题，二是效率有点低下。

[wwwwb]

呵呵，看来你只有升级MYSQL了，4.0居然不支持子查询。

[Kuye2002yx]

[Quote=引用 10 楼 wwwwb 的回复:]
呵呵，看来你只有升级MYSQL了，4.0居然不支持子查询。
[/Quote]
嗯。。。看样子只好这样了。 -_-#

[Kuye2002yx]

结果是问了下领导，说不用把那五个人从全部人里单拎出来也行~

结贴，散分！ :D