我有两张表，即待定票和待定候选人：
待定投票：包含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_候选包含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？
非常感谢你的大力帮助。谢谢您！
最佳答案
SELECT fName||' '||mName||' '||lName, votes FROM (
SELECT a.idcandidate, COUNT(b.idcandidate) votes
FROM tbl_candidates a
LEFT JOIN (
SELECT president idcandidate from tbl_votes
UNION ALL
SELECT vicePresident idcandidate from tbl_votes
UNION ALL
SELECT secretary idcandidate from tbl_votes
UNION ALL
SELECT treasurer idcandidate from tbl_votes
UNION ALL
SELECT rep1 idcandidate from tbl_votes
UNION ALL
SELECT rep2 idcandidate from tbl_votes
UNION ALL
SELECT rep3 idcandidate from tbl_votes ) b
ON (a.idcandidate = b.idcandidate)
GROUP BY a.idcandidate ) tab
JOIN tbl_candidates b on (b.idcandidate = tab.idcandidate)
上面的答案是针对SQLite的，我不知怎的误读了这个问题的标签。
但是它可能工作，除了第一行需要使用mySQL格式：
SELECT CONCAT_WS(" ", fName, mName, lName), votes FROM (