我要取出票数最高的select id,translation,original_textID,votes,languageID from mts_translation1 where languageID=1 group by original_textID having max(votes)这样写不对啊~~
select id,translation,original_textID,votes,languageID from mts_translation1 mt where languageID=1 and not exists (select 1 from mts_translation1 where original_textID=mt.original_textID and votes>mt.votes);
select mt.id,mt.translation,mt.original_textID,mt.votes,mt.languageID from mts_translation1 mt, (select original_textID,max(votes) vote from mts_translation1 group by original_textID) mgt where mt.languageID=1 and mt.votes=mgt.vote and mt.original_textID=mgt.original_textID;
语法没错 可结果不对 得出的同一组original_textID的票数不是最高且不唯一
select mt.id,mt.translation,mt.original_textID,mt.votes,mt.languageID from mts_translation1 mt, (select original_textID,max(votes) vote from mts_translation1 group by original_textID) mgt where mt.votes=mgt.vote and mt.original_textID=mgt.original_textID;
还是不行 出来的original_textID还不是唯一的
select id,translation,original_textID,votes,languageID from mts_translation1 where languageID=1 and votes=(select max(votes) from mts_translation1 )group by original_textID
//看来楼主要多用点心,已经有这么多哥哥帮助你了,加油! SELECT translation,languageID,original_textID,max(votes) votes from mts_translation1 where languageID=1 group by original_textID
SELECT translation,languageID,original_textID,max(votes) votes from mts_translation1 where languageID=1 group by original_textID//试下,下班(17:00)前给我个回信~~~
select id,translation,original_textID,votes,languageID from mts_translation1 where languageID=1 order by votes DESC limit 1; 最简单的找到票数最高的: order by votes DESC 。
select mt.id,mt.translation,mt.original_textID,mt.votes,mt.languageID, (select count(*) from mts_translation1 where languageID=1 and votes=mt.votes and original_textID=mt.original_textID and id<mt.id) ct from mts_translation1 mt, (select original_textID,max(votes) vote from mts_translation1 group by original_textID) mgt where mt.languageID=1 and mt.votes=mgt.vote and mt.original_textID=mgt.original_textID and ct=0;
select mt.id,mt.translation,mt.original_textID,mt.votes,mt.languageID from mts_translation1 mt, (select original_textID,max(votes) vote from mts_translation1 group by original_textID) mgt where mt.languageID=1 and mt.votes=mgt.vote and mt.original_textID=mgt.original_textID and 0=(select count(*) from mts_translation1 where languageID=1 and votes=mt.votes and original_textID=mt.original_textID and id <mt.id);
辛苦了~~~你这个也太。。sql编辑器都死那了 估计它也打结了
SELECT * FROM ( SELECT * FROM mts_translation1 ORDER BY votes DESC )T GROUP BY original_textID 这就能取得最高投票的了
where languageID=1
and not exists
(select 1 from mts_translation1 where original_textID=mt.original_textID and votes>mt.votes);
from mts_translation1 mt,
(select original_textID,max(votes) vote from mts_translation1 group by original_textID) mgt
where mt.languageID=1 and mt.votes=mgt.vote and mt.original_textID=mgt.original_textID;
from mts_translation1 mt,
(select original_textID,max(votes) vote from mts_translation1 group by original_textID) mgt
where mt.votes=mgt.vote and mt.original_textID=mgt.original_textID;
还是不行 出来的original_textID还不是唯一的
1 aaaaa 1 1 6
2 aaa``` 1 1 2
3 ccc 1 1 1
4 dddd 1 2 16
5 rrrr 1 2 8
6 kkk 1 2 1
7 替 2 1 5最后要得到
id translation languageID original_textID votes
1 aaaaa 1 1 6
4 dddd 1 2 16这样的结果
select id,translation,original_textID,max(votes),languageID from mts_translation1 group by id,original_textID
1 aaaaa 1 1 6
2 aaa``` 1 1 2
3 ccc 1 1 1
4 dddd 1 2 16
5 rrrr 1 2 8
6 sfsfs 1 3 1
7 sfsf 1 3 1
8 eeee 1 3 1结果就为
id translation languageID original_textID votes
1 aaaaa 1 1 6
4 dddd 1 2 16
6 sfsfs 1 3 1
7 sfsf 1 3 1
8 eeee 1 3 1
SELECT translation,languageID,original_textID,max(votes) votes from mts_translation1 where languageID=1 group by original_textID
order by votes DESC limit 1; 最简单的找到票数最高的: order by votes DESC 。
不对哦 你这样我以前也试过 这样只是取了高票数 但不是对应的项就是这样
id translation languageID original_textID votes
1 aaaaa 1 1 1
2 aaa``` 1 1 2
3 ccc 1 1 6
4 dddd 1 2 1
5 rrrr 1 2 8
6 kkk 1 2 16
结果
id translation languageID original_textID votes
1 aaaaa 1 1 6
4 dddd 1 2 16
(select count(*) from mts_translation1 where languageID=1 and votes=mt.votes
and original_textID=mt.original_textID and id<mt.id) ct
from mts_translation1 mt,
(select original_textID,max(votes) vote from mts_translation1 group by original_textID) mgt
where mt.languageID=1 and mt.votes=mgt.vote and mt.original_textID=mgt.original_textID
and ct=0;
from mts_translation1 mt,
(select original_textID,max(votes) vote from mts_translation1 group by original_textID) mgt
where mt.languageID=1 and mt.votes=mgt.vote and mt.original_textID=mgt.original_textID
and 0=(select count(*) from mts_translation1 where languageID=1 and votes=mt.votes
and original_textID=mt.original_textID and id <mt.id);
辛苦了~~~你这个也太。。sql编辑器都死那了 估计它也打结了
FROM (
SELECT *
FROM mts_translation1
ORDER BY votes DESC
)T
GROUP BY original_textID
这就能取得最高投票的了
id translation languageID original_textID votes
1 aaaaa 1 1 1
2 aaa 1 1 2
3 ccc 1 1 6
4 ddd 1 2 1
5 eeeee 1 2 8
6 ssss 1 2 16
7 ee 1 3 9
8 aaa 1 3 4
[/phpcode]如果记录是这样子的话,那么你要一条语句查出来是这个样子的,是吧?
[phpcode]
id translation languageID original_textID votes
3 ccc 1 1 6
6 ssss 1 2 16
7 ee 1 3 9
[/phpcode]