mysql - 这个基本的sql语句怎么写

Question

所以我有 4 个通过外键连接的表，即 result、position、student、candidates

我需要实现的是:输出:

------------------------
 s_fname | count(c_id)
-----------------------
   Mark  |     2       -> President
   France|     2       -> President

.. 计算 c_id 在表“result”中重复了多少次，该表也由“candidates”表中的 pos_id 过滤

下面是我的代码，缺少计数部分:

select s_fname 
from results, candidates, student, positioning 
where results.c_id = candidates.c_id 
  AND student.sid = results.sid 
  AND candidates.pos_id = positioning.pos_id 
  AND positioning.pos_id = 1 
Group BY results.sid;

..我知道它缺少很多东西......

谢谢

这对我来说似乎很复杂，但我知道这里有专家可以做到这一点，

结果表

---------------------
| r_id | sid | c_id |
---------------------
    1   |  1  |   1
    2   |  1  |   2
    3   |  1  |   4
    4   |  2  |   1
    5   |  2  |   2
    6   |  2  |   4
    7   |  3  |   3
    8   |  3  |   2
    9   |  5  |   3
    10  |  5  |   2

----------------------
student table
----------------
| s_id| s_fname|
----------------
    1  |  Mark
    2  |  Jorge
    3  |  France
    4  |  James

--------------------
Candidates Table
------------------------
| c_id | sid  |  pos_id
------------------------
    1   |   1  |     1
    2   |   2  |     2
    3   |   4  |     3
    4   |   3  |     1
    5   |   5  |     2


----------------------
positioning Table
-----------------------
| pos_id  |  po_name |
-----------------------
 1    |  President
 2    |  Vice President
 3    |  Secretary
 4    |  Treasurer

Answer 1

这是未经测试的，但应该会返回您想要的结果。

它的作用是在相关的外键 上连接所有表，有效地提供一个包含所有列的宽表。然后我们限制正在竞选 President 职位的 candidates。因为我们需要group 因为count aggregate 我们group name。 count 应该反射(reflect)他们获得的选票数量，因为 result 表存在一对多关系。

SELECT s_fname, Count(*) 
FROM studentTable st 
INNER JOIN Candidates c On c.sid = st.s_ID 
INNER JOIN positioning p on c.pos_ID = p.pos_ID
INNER JOIN results r on st.s_ID = r.s_ID
WHERE po_Name = "President"
GROUP BY s_Fname 

由于对预期连接的误解，以下查询应显示适当的结果。

SELECT s_fname, Count(*) 
FROM studentTable st 
INNER JOIN Candidates c On c.sid = st.s_ID 
INNER JOIN positioning p on c.pos_ID = p.pos_ID
INNER JOIN results r on c.c_ID = r.c_ID
WHERE po_Name = "President"
GROUP BY s_Fname