好吧,我放弃了,这个问题超出了我的理解,我正在寻求帮助。
在MySQL数据库中,我有一个类别和一个帖子表,就像一个标准的博客系统:
tblCategories (intID (PK), strTitle)
tblAreas (intID (PK), strName)
tblPosts (intID (PK), intCategoryID (FK), strTitle, htmlText)
然后我有几个博客支持表:
tblExternalLinks (intID (PK), intPostID (FK), strTitle, urlLink)
tblComments (intID (PK), intPostID (FK), strComment)
tblImages (intID (PK), intPostID (FK), urlImage)
tblLocations (intID (PK), intPostID (FK), intAreaID (FK), urlImage)
我想要的是附加到一个类别变量的所有帖子的列表(让我们说在伪php代码)$intCatID, AND有一个区域变量$intAreaID。这个SQL应该是比如:
谁能告诉我这是对的吗?我不太确定SELECT tblPosts.* from tblPosts INNER JOIN tblLocations ON tblLocations.intPostID=tblPosts.intID WHERE intCategoryID=$intCatID AND intAreaID=$intAreaID GROUP BY tblPosts.intID;
我想要相同的列表,但这次与计数所有其他附在支持表中的记录:
SELECT tblPosts.*, intExternalLinksCount, intCommentsCount, intImagesCount, intLocationsCount ...
我的第一个猜测是使用嵌套表检索计数,或者join,这可能会使查询非常大。我也想知道什么是更快的性能。这些东西是我做爱的地方头。
正因为我喜欢收到批评的反馈,我想出了这个(非常,非常丑陋的)查询:
SELECT
tblPosts.*,
tblTempLocations.intLocationsCount,
tblTempLinks.intLinksCount,
tblTempImages.intImagesCount,
tblTempComments.intCommentsCount,
tblTempNearest.fltNearestLocationAngle
FROM tblAdverts
LEFT JOIN
(SELECT intPostID, count(*) as intLocationsCount
FROM tblLocations
GROUP BY intPostID)
tblTempLocations ON tblPosts.intID=tblTempLocations.intPostID
LEFT JOIN
(SELECT intPostID, count(*) as intLinksCount
FROM tblLinks
GROUP BY intPostID)
tblTempLinks ON tblPosts.intID=tblTempLinks.intPostID
LEFT JOIN
(SELECT intPostID, count(*) as intImagesCount
FROM tblImages
GROUP BY intPostID)
tblTempImages ON tblPosts.intID=tblTempImages.intPostID
LEFT JOIN
(SELECT intPostID, count(*) as intReviewsCount
FROM tblComments
GROUP BY intPostID)
tblTempComments ON tblPosts.intID=tblTempComments.intPostID
LEFT JOIN
(SELECT intPostID,
MIN(SQRT(POWER((fltLatitude - $fltCurrentLatitude), 2) + POWER((fltLongitude - $fltCurrentLongitude),2))) AS fltNearestLocationAngle
FROM tblLocations
GROUP BY intPostID)
tblTempNearest ON tblPosts.intID=tblTempNearest.intPostID
INNER JOIN tblLocations ON tblPosts.intID=tblLocations.intPostID
WHERE tblLocations.intLocationID=$intAreaID AND tblPosts.strTitle LIKE '%$strPartialTitle%' AND tblAdverts.intCategoryID=$intCatID
GROUP BY tblAdverts.intID