我有一张有6个字段的表:
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
我想用“count”和一行“SUM”将partyname、选区分组
但它只计算每个选区的最大值,然后显示计数和行和。
下表是我对上表的预期输出
partyname Count
BJP 2
AAP 1
bcoz bjp从那格浦尔以东和那格浦尔以西引出,所以显示计数2
从那格浦尔北部的aap显示只有1个计数。
请建议我查询。。。。。。
<?php
$state = $rowdst['state'];
$sqlst = "SELECT MAX(votes) AS Vote,constituency,state, district FROM voter_count where state = '$state' group by constituency, state";
$resultst = mysql_query($sqlst);
while($rowst = mysql_fetch_array($resultst,MYSQL_ASSOC))
{
?>
<?php
$vote = $rowst['Vote'];
$sqlct = "SELECT COUNT($vote) AS counts,partyname,constituency, district FROM voter_count where votes = '$vote' AND state = '$state'";
$resultct = mysql_query($sqlct);
$rowct = mysql_fetch_array($resultct,MYSQL_ASSOC)
?>
<tr>
<td><?php echo $rowct['partyname']; ?></td>
<td><?php echo $rowct['counts']; ?></td>
</tr>
<?php } ?>
最佳答案
假设您不关心不同政党的相同票数(处理同一地区不同政党的相同票数将使查询复杂得多,因为您将在内部查询中为MAX
列获得不可预测的值):
SELECT RES.PARTYNAME, COUNT(RES.CONSTITUENCY) AS VOTESCOUNT
FROM voter_count RES
JOIN (
SELECT CONSTITUENCY, MAX(VOTES) AS VOTES
FROM voter_count
GROUP BY CONSTITUENCY
) MAXS USING(VOTES, CONSTITUENCY)
GROUP BY PARTYNAME;
首先,内部查询为
votes
选择最大值。内部查询执行的结果是:Nagpur-East,1200
Nagpur-North,1500
Nagpur-West,4800
之后,加入最大值和计数区。
AAP,1
BJP,2