添加第三个表的加入
问题描述:
此查询工作正常:添加第三个表的加入
$sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, l.username
FROM submission AS s,
login AS l
WHERE s.loginid = l.loginid
ORDER BY s.datesubmitted DESC
LIMIT 10";
将这项工作,如果我想加入第三MySQL表(称为“评论”)呢?
$sqlStr = "SELECT s.loginid, s.submissionid s.title, s.url, s.displayurl, l.username, count(c.comment) countComments
FROM submission AS s,
login AS l,
comment AS c,
WHERE s.loginid = l.loginid
AND s.submissionid = c.submissionid
ORDER BY s.datesubmitted DESC
LIMIT 10";
由于提前,
约翰
答
你真的应该使用连接,它更清晰,它让你的加入条件,你的where子句中。此外,当你想在添加第三个表,它更是一致的:
这是你的原代码,使用显式联接语法:
SELECT s.loginid, s.title, s.url, s.displayurl, l.username
FROM submission s
INNER JOIN login l ON l.loginid = s.loginid
ORDER BY s.datesubmitted DESC
LIMIT 10
然后,如果你想添加第三个表,很容易:
SELECT s.loginid, s.title, s.url, s.displayurl, l.username
FROM submission s
INNER JOIN login l ON l.loginid = s.loginid
INNER JOIN comment c ON s.submissionid = c.submissionid
ORDER BY s.datesubmitted DESC
LIMIT 10
另外,这里有一个技巧。如果你加入的是同一个名字的两列,您可以使用USING
语法:
INNER JOIN login l USING (loginid)
INNER JOIN comment c USING(submissionid)
答
差不多。
您的语法是为第三个表是正确的(虽然是旧式;它使用隐式联接)。
但是为了让COUNT(c.comment)能够工作,您必须(或者我应该说“应该”,因为MySQL在某些语法上是松散的)为选择添加一个GROUP BY子句。这条款应命名在SELECT每隔一列:
SELECT s.loginid, s.submissionid s.title, s.url,
s.displayurl, l.username, count(c.comment) AS countComments
FROM submission AS s,
login AS l,
comment AS c,
WHERE s.loginid = l.loginid AND s.submissionid = c.submissionid
GROUP BY s.loginid, s.submissionid s.title, s.url, s.displayurl, l.username
ORDER BY s.datesubmitted DESC
LIMIT 10
答
你将不得不增加一个GROUP BY子句与它(我只是设置在下面的例子中的登录ID)一个或多个字段以及你在那里使用COUNT()函数,所以SQL会像这样:
$sqlStr = "SELECT s.loginid, s.submissionid s.title, s.url, s.displayurl, l.username, count(c.comment) countComments
FROM submission AS s,
login AS l,
comment AS c
WHERE s.loginid = l.loginid
AND s.submissionid = c.submissionid
GROUP BY s.loginid
ORDER BY s.datesubmitted DESC
LIMIT 10";