我一直在尝试每一个技巧，我可以完成我所寻找的。我不是新手，但我被困在这上面了。我试图使数据的"网格"从3个表。我想让它看起来像这样:
Assignments | Assgn 1 | Assgn 2 | Assgn 3 | Assgn 4 | Final Grade
----------------------------------------------------------------------
Username 1 | Grade | Grade | Grade | Grade | Total
----------------------------------------------------------------------
Username 2 | Grade | Grade | Grade | Grade | Total
----------------------------------------------------------------------
Username 3 | Grade | Grade | Grade | Grade | Total
成员的第一个表列:
Memberid, Username
赋值表:
id, title
分级保存:
id,assign,student,grade
assign是赋值表中赋值的id号student是成员表中的Memberid。
这几天我一直在调查这件事。
给出一些结果的是这个查询
$result = mysql_query("SELECT assignments.title,subassign.grade, members.Username FROM assignments, subassign, members WHERE members.Memberid = subassign.student and assignments.id = subassign.assign ",$connect);
它正确地链接了信息，但我不能让它像我想要的那样显示记录。它只是在php中创建一个新行。有人能帮帮我吗?我要把我的电脑扔出窗外。
您可以像这样使用join:
SELECT
members.title AS 'Assignments',
assignment1.grade AS 'Assign1',
assignment2.grade AS 'Assign2',
assignment3.grade AS 'Assign3',
assignment3.grade AS 'Assign4'
FROM members
INNER JOIN subassign AS assignment1 ON assignment1.person = members.MemberId AND assign = 1
INNER JOIN subassign AS assignment2 ON assignment2.person = members.MemberId AND assign = 2
INNER JOIN subassign AS assignment3 ON assignment3.person = members.MemberId AND assign = 3
INNER JOIN subassign AS assignment4 ON assignment4.person = members.MemberId AND assign = 4
注意，如果您希望根据表中的assignments返回动态列(我假设您是这样做的)，那么最好的方法是动态生成一个类似于上面的查询。请参阅下面的一些非常粗略的示例代码，这些代码将生成您想要的动态查询(这是完全未经测试的):
$results = mysql_query("SELECT id, title FROM assignments");
$query = "SELECT
members.title AS 'Assignments',";
$joins = "";
while($row = mysql_fetch_assoc($results))
{
$name = 'assignment' . $row['id'];
$query .= "'r'n" . $name . ".grade AS '" . $row['title'] . "',";
$joins .= "'r'nLEFT JOIN subassign AS " . $name . " ON " . $name . ".person = members.MemberId AND " . $name . ".assign = " . $row['id'];
}
$query = substr($query, 0, -1) . " FROM members" . $joins;
$result = mysql_query($query);
不能100%确定我是否得到了查询的权利，但你得到的想法:
SELECT
a.title,
s.grade,
m.Username
FROM
members m
LEFT JOIN
subassign AS s ON s.student = m.Memberid
LEFT JOIN
assignments AS a ON a.id = s.assign
这基本上是数据的枢轴，但是MySQL没有枢轴。因此，您可以通过使用具有CASE的聚合函数来复制该功能，如下所示:
select m.username as assignments,
max(case when s.assign = 1 then s.grade end) Assign1,
max(case when s.assign = 2 then s.grade end) Assign2,
max(case when s.assign = 3 then s.grade end) Assign3,
max(case when s.assign = 4 then s.grade end) Assign4,
avg(s.grade) FinalGrade
from members m
left join subassign s
on m.memberid = s.student
left join assignments a
on s.assign = a.id
group by m.username
参见SQL Fiddle with Demo
结果:| ASSIGNMENTS | ASSIGN1 | ASSIGN2 | ASSIGN3 | ASSIGN4 | FINALGRADE |
--------------------------------------------------------------------
| username 1 | 50 | 68 | 32 | 68 | 54.5 |
| username 2 | 75 | 86 | 89 | 36 | 71.5 |
| username 3 | 99 | 70 | 24 | 100 | 73.25 |