mysql - SQL - 从 2 个或更多列计数

Question

我是 SQL 的新手，我有一个选举申请。我已经完成了其中的 80%，现在还在计算来自 2 个或更多列的选票。

示例表:

|**Senator_1** | **Senator_2** | **Senator_3**|
----------------------------------------------
   George      |    Carl       |   Anthony
   Carl        |    Tony       |   Stark
   Anthony     |    George     |   Tony
   Anthony     |    George     |   Stark

我想要这样的结果。

|**Candidate_Name** | **Vote_Count**|
-------------------------------------
   George           |   3
   Anthony          |   3
   Carl             |   2
   Stark            |   2
   Tony             |   2

我真的不知道要使用什么查询。有解决这个问题的想法吗？

顺便说一下，对于从这里开始的困惑和所有争论，我将解释:

我想直截了本地解决我的问题，这就是我刚刚发布示例表格的原因。我有一张用于选民、候选人和选票的表格。所有表都有其 ID 等，所以我很确定它是规范化的。

Answer 1

您遇到的主要问题是您的表未规范化。我强烈建议您修复当前的表结构。 可能的新表结构是:

/* Table for unique voters */
CREATE TABLE voters (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL 
) ENGINE=InnoDB;

/* Table for unique candidates */
CREATE TABLE candidates (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

/* Many-to-many table storing votes by voters for candidates */
CREATE TABLE votes (
    voter_id INT UNSIGNED NOT NULL,
    candidate_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (voter_id, candidate_id),
    CONSTRAINT FOREIGN KEY (voter_id) REFERENCES voters (id),
    CONSTRAINT FOREIGN KEY (candidate_id) REFERENCES candidates (id)
) ENGINE=InnoDB;

/* Populate data */
INSERT INTO voters (name)
VALUES ('Voter 1'), ('Voter 2'), ('Voter 3'), ('Voter 4');

INSERT INTO candidates (name)
VALUES ('George'), ('Carl'), ('Anthony'), ('Tony'), ('Stark');

INSERT INTO votes (voter_id, candidate_id)
VALUES (1,1), (1,2), (1,3),
       (2,2), (2,4), (2,5),
       (3,3), (3,1), (3,4),
       (4,3), (4,1), (4,5);

然后你可以很容易地通过连接两个表得到一个结果:

/* query showing all voters and the candidates they voted for */
SELECT voters.name, candidates.name
FROM votes
    INNER JOIN voters on votes.voter_id = voters.id
    INNER JOIN candidates ON votes.candidate_id = candidates.id;

/* Finally, a query showing votes per candidate */
SELECT candidates.name, COUNT(*) AS votes
FROM votes
    INNER JOIN candidates ON votes.candidate_id = candidates.id
GROUP BY candidates.id;

参见 SQL Fiddle with Demo

但是，如果您无法更改表的设计，则可以通过对多列中的数据进行逆透视来获得结果。您可以使用 UNION ALL 将多列反透视为行以获取计数:

select name, count(*) TotalVotes
from
(
  select senator_1 name
  from yt
  union all
  select senator_2 name
  from yt
  union all
  select senator_3 name
  from yt
) d
group by name
order by totalVotes desc;

参见 SQL Fiddle with Demo