mysql SQL查询从表中只选择最大值的计数

pxyaymoc 于 2023-10-15 发布在 Mysql

关注(0)|答案(3)|浏览(100)

partyname    state        constituency     candidatename   district    votes

BJP       Maharashtra   Nagpur-East     Nitin Gadkari   Nagpur       1200
AAP       Maharashtra   Nagpur-East     Arvind Kejriwal Nagpur       750
BJP       Maharashtra   Nagpur-West     Vicky           Nagpur       4800
AAP       Maharashtra   Nagpur-West     Lucky           Nagpur       3500
BJP       Maharashtra   Nagpur-North    Rakesh          Nagpur       100
AAP       Maharashtra   Nagpur-North    Ravan          Nagpur       1500

下面是我对上表的预期输出
查询只选择最大vlue从投票组由政党名称和选区和显示如下输出.

partyname       Count

BJP          2

AAP          1

bcoz bjp导致从纳格布尔-东和那格浦尔西
所以它显示计数2
和aap导致从纳格布尔-北，那么它只显示1计数。

plz suggest me query.

mysql

来源：https://stackoverflow.com/questions/26294021/sql-query-to-select-count-of-only-max-value-from-table

3条答案

按热度按时间

sqyvllje1#

你可以这样尝试：

select max(votes) from tableName group by constituency

SQL fiddle：http：//sqlfiddle.com/#！2/ceb017/1

赞(0）回复(0）举报 2023-10-15

3ks5zfa02#

在MySQL中，你需要一个join来获得最大投票数，然后另一个join来获得政党名称和获胜次数：

select c.partyname, count(*) as numwins
from constituency c join
     (select constituency, max(votes) as maxvotes
      from constituency c
      group by constituency
     ) cm
     on c.constituency = cm.constituency and c.votes = cm.votes
group by c.partyname;

赞(0）回复(0）举报 2023-10-15

7eumitmz3#

SELECT STATE FROM CONSTRUENCY GROUP BY STATE HAVING（STATE）=（SELECT MAX（COUNTS）FROM（SELECT COUNTS（*）COUNTS FROM CONSTRUENCY GROUP BY STATE）COUNTER）;

赞(0）回复(0）举报 2023-10-15