SQL查询来计算某些值在多行中出现的次数 [英] SQL query to count number of times certain values occur in multiple rows

问题描述

假设我有一张选举数据表，称为选举，每次选举每个选民都有一行，就像这样:

VoterID ElectionID
A           1
A           2
B           1
C           2
D           3
E           1
E           2

我想知道在选举1和选举2中都进行投票的选民人数；我不在乎别人该数字应为2(选民A和选民E).

会进行以下工作吗?

select count(Elections) as NumVoters
from (
select VoterID, ElectionID, count(ElectionID) as Elections
from ELECTIONS
where ElectionID=1 or ElectionID=2
group by VoterID
having (count(ElectionID)=2)
) x;

更新:这是我在这里的第一个问题，而我对工作人员的帮助和快感感到震惊.我修改了上面的查询，以解决最后缺少别名并添加终止分号的情况.

谢谢！

解决方案

是.你所拥有的应该工作. (您将需要在派生表上添加一个别名，您得到的错误消息应该是不言自明的.易于修复，只需在查询末尾添加一个空格和字母c(或您想要的任何名称)即可.

关于重复出现(VoterID, ElectionID)元组的可能性，有一个警告.

如果您对(VoterID，ElectionID)有唯一的约束，那么您的查询将正常工作.

如果您没有唯一的约束(不允许重复的(VoterID, ElectionId))，则该选民有可能有两(2)行用于ElectionID 1，而该行没有用于ElectionID 2 ...计入计数.在ElectionID 1中进行了两次投票而在ElectionID 2中进行过一次投票的选民，该选民将被排除在计数之外.

在COUNT中包含DISTINCT关键字可以解决该问题，例如

HAVING COUNT(DISTINCT ElectionID) = 2

---

我将以不同的方式编写查询，但是您所拥有的将起作用.

为了获得参与ElectionID 1和ElectionID2的VoterID的计数，以提高性能，我避免使用内联视图(MySQL称其为派生表).我会让查询改为使用JOIN操作.像这样:

SELECT COUNT(DISTINCT e1.voterID) AS NumVoters
  FROM elections e1
  JOIN elections e2
    ON e2.voterID = e1.voterID
 WHERE e1.electionID = 1
   AND e2.electionID = 2

如果确保(voterID, ElectionID)是唯一的，那么选择可能会更简单:

SELECT COUNT(1) AS NumVoters
  FROM elections e1
  JOIN elections e2
    ON e2.voterID = e1.voterID
 WHERE e1.electionID = 1
   AND e2.electionID = 2

Suppose I have a table of election data, call it ELECTIONS, with one row per voter per election, like so:

VoterID ElectionID
A           1
A           2
B           1
C           2
D           3
E           1
E           2

I want to know the number of voters who voted both in election 1 and in election 2; I don't care about anyone else. The number should be 2 (voter A and Voter E).

Would something like this work:

select count(Elections) as NumVoters
from (
select VoterID, ElectionID, count(ElectionID) as Elections
from ELECTIONS
where ElectionID=1 or ElectionID=2
group by VoterID
having (count(ElectionID)=2)
) x;

UPDATE: This is my first question here, and I am blown away at how helpful and fast folks have been. I revised the query above to fix the lack of an alias at the end and to add a terminating semicolon.

THANK YOU!

解决方案

Yes. what you have should work. (You will need to add an alias on the derived table, the error messsage you get should be self explanatory. Easy to fix, just add a space and the letter c (or whatever name you want) at the end of your query.

There's one caveat regarding the potential for duplicate (VoterID, ElectionID) tuples.

If you have a unique constraint on (VoterID, ElectionID), then your query will work fine.

If you don't have a unique constraint (which disallows duplicate (VoterID, ElectionId)), then there's a potential for a voter with two (2) rows for ElectionID 1, and no rows for ElectionID 2... for that voter to get included in the count. And a voter that voted twice in ElectionID 1 and only once in ElectionID 2, that voter will be excluded from the count.

Including the DISTINCT keyword inside a COUNT would fix that problem, e.g.

HAVING COUNT(DISTINCT ElectionID) = 2

---

I'd write the query differently, but what you have will work.

To get the count of VoterID that participated in both ElectionID 1 and ElectionID2, for improved performance, I'd avoid using an inline view (MySQL calls it a derived table). I'd have the query use a JOIN operation instead. Something like this:

SELECT COUNT(DISTINCT e1.voterID) AS NumVoters
  FROM elections e1
  JOIN elections e2
    ON e2.voterID = e1.voterID
 WHERE e1.electionID = 1
   AND e2.electionID = 2

If you are guaranteed that (voterID, ElectionID) is unique, then the select could be simpler:

SELECT COUNT(1) AS NumVoters
  FROM elections e1
  JOIN elections e2
    ON e2.voterID = e1.voterID
 WHERE e1.electionID = 1
   AND e2.electionID = 2

这篇关于SQL查询来计算某些值在多行中出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！