mysql - 如何选择带有计数的 1 列的多列

Question

我有两个表，即 tbl_votes 和 tbl_candidates:

tbl_votes:包含 ff 列:

voteID    president    vicePresident    secretary    treasurer    rep1    rep2     rep3
1             1              3                                     9       12       15
2             1              4              6            8         12      15
3             2              3              5            7         9       12

当 tbl_candidates 包含 ff 列时:

idcandidate  fName         mName         lName         position
1            Jefferson     Garcia        Roxas         president
2            Carlson       Mcvoy         Zafiro        president
3            Andrew        Efron         Anderson      vice president
4            Jessica       Garcia        Roxas         secretary

...

9            Jayson        Sanchez       Dela Cruz      representative
10           Arille Alisa  Villanueva    Valdez         representative
11           Arnold        Castro        Baltazar       representative
12           Kristina      de Leon       Guillermo      representative
13           Jayson        Jones         Dela Cruz      representative
14           Karla         Santos        Abrogena       representative
15           Jason                       Chua           representative

我想要的查询是:

Name                                                       votes
Jefferson Garcia Roxas                                       2
Carlson Mcvoy Zafiro                                         1
Andrew Efron Anderson                                        2
Jessica Garcia Roxas                                         1

...

Jayson Sanchez Dela Cruz                                     2
Arnold Castro Baltazar                                       0
Kristina de Leon Guillermo                                   3
Jason  Chua                                                  2

我不知道在哪里计票，这是我的代码:

SELECT `Name`, Votes
FROM (SELECT COUNT(*) as Votes FROM tbl_votes) a
CROSS JOIN (Select (Concat(fName ,' ',mName,' ', lName)) as `Name` from tbl_candidates) b;

这个查询的结果是:

Name                                                       Votes
Jefferson Garcia Roxas                                       3
Carlson Mcvoy Zafiro                                         3
Andrew Efron Anderson                                        3
Jessica Garcia Roxas                                         3
Jayson Sanchez Dela Cruz                                     3
Arnold Castro Baltazar                                       3
Kristina de Leon Guillermo                                   3
Jason  Chua                                                  3

我将如何针对 idcandidate 进行计数？非常感谢您的大力帮助。谢谢!

Answer 1

虽然您可以通过CROSS JOIN(但与您建议的不同)和适当的结果聚合来解决这个问题，但这是一种无法扩展的糟糕方法出色地。当然，当您像现在这样背负着糟糕的数据模型时，并没有真正好的方法。

有几种方法可以解决这个问题，但没有一种特别好，例如:

SELECT `Name`, COUNT(*) AS `votes`
FROM
(
  SELECT
    CONCAT(fName, ' ', mName, ' ', lName) as `Name`
  FROM
    tbl_candidates c
    JOIN tbl_votes v
      ON c.idcandidate = v.president
  WHERE
    c.position = 'president'

  UNION ALL

  SELECT
    CONCAT(fName, ' ', mName, ' ', lName) as `Name`
  FROM
    tbl_candidates c
    JOIN tbl_votes v
      ON c.idcandidate = v.vicePresident
  WHERE
    c.position = 'vice president'

  UNION ALL

  SELECT
    CONCAT(fName, ' ', mName, ' ', lName) as `Name`
  FROM
    tbl_candidates c
    JOIN tbl_votes v
      ON c.idcandidate IN (v.rep1, v.rep2, v.rep3)
  WHERE
    c.position = 'representative'
) vote_agg
GROUP BY `Name`

这按职位分解问题，为每个职位使用一个内联 View ，为该职位的每位候选人的每次投票生成一行。然后通过UNION ALL将它们组合成一个整体列表，并对结果执行聚合查询以计算每个候选人的选票。

如果现有候选人没有为他们未竞选的职位投票(这很难或不可能通过对指定数据模型的约束来防止)，那么这些将被忽略。如果任何一张选票对同一代表候选人投了多于一票，则只会计算一张(可能是期望的行为，也可能不是)。