id logid counts
1 jeff 0
2 jeff 10
3 jeff 0
4 face 8
5 face 0
6 jeff 1
7 jeff 0
8 face 7
9 jeff 0
10 jeff 0
11 jeff 0
12 xixi 6
13 xixi 2
14 xixi 11
如何用最简单的sql来查询不同logid,counts最大的记录,并按倒序排列,结果如:
14 xixi 11
2 jeff 10
4 face 8
1 jeff 0
2 jeff 10
3 jeff 0
4 face 8
5 face 0
6 jeff 1
7 jeff 0
8 face 7
9 jeff 0
10 jeff 0
11 jeff 0
12 xixi 6
13 xixi 2
14 xixi 11
如何用最简单的sql来查询不同logid,counts最大的记录,并按倒序排列,结果如:
14 xixi 11
2 jeff 10
4 face 8
*
From
TableName A
Where
Not Exists(Select counts From TableName Where logid = A.logid And counts > A.counts)
Order By
counts Desc
from 表
group by logid
order by max(id) desc,max(counts) desc
*
From
TableName A
Where
counts = (Select Max(counts) From TableName Where logid = A.logid)
Order By
counts Desc
A.*
From
TableName A
Inner Join
(Select logid, Max(counts) As counts From TableName Group By logid) B
On A.logid = B.logid And A.counts = B.counts
Order By
A.counts Desc
select max(id) as 'id',logid,max(counts) as 'counts'
from 表
group by logid
order by max(id) desc,max(counts) desc
---------有錯誤。 :)
Select
*
From
TableName A
Where
Not Exists(Select counts From TableName Where logid = A.logid And counts > A.counts)
Order By
counts Desc
--方法二
Select
*
From
TableName A
Where
counts = (Select Max(counts) From TableName Where logid = A.logid)
Order By
counts Desc
--方法三
Select
A.*
From
TableName A
Inner Join
(Select logid, Max(counts) As counts From TableName Group By logid) B
On A.logid = B.logid And A.counts = B.counts
Order By
A.counts Desc
鱼的写法对,学习.
FROM TableName A INNER JOIN
(SELECT logid, MAX(counts) AS counts
FROM TableName
GROUP BY logid) B ON A.logid = B.logid AND A.counts = B.counts
ORDER BY A.counts DESC这个应该是效率最高的。