我有两个看起来像这样的表;
Table: pictures
`picture_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`picture_title` varchar(255) NOT NULL,
`picture_description` text NOT NULL,
`picture_src` varchar(50) NOT NULL,
`picture_filetype` varchar(10) NOT NULL,
`picture_width` int(11) NOT NULL,
`picture_height` int(11) NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`upload_date` datetime NOT NULL,
--
Table: picture_votes
`vote_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`picture_id` int(10) unsigned NOT NULL,
`vote` tinyint(4) NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`timestamp` datetime NOT NULL,
我想做的是从表中选择pictures字段，然后计算picture_votes中的所有记录，其中pictures.picture_id = picture_votes.picture_id，例如;
picture_id => 1
picture_title => 'Pic title'
picture_description => 'Pic description'
picture_src => 'b8b3f2c3a85f1a46fbf2ee132d81f783'
picture_filetype => 'jpg'
picture_width => 612
picture_height => 612
user_id => 1
upload_date => '2013-10-12 12:00:00'
vote_count => 3 // Amount of records in `picture_votes` that has `picture_id` = 1
我想出了（其中$limit是要选择的图片数量）;
SELECT pictures.*, count(picture_votes.vote) as vote_count
FROM pictures, picture_votes
WHERE pictures.picture_id = picture_votes.picture_id
ORDER BY upload_date DESC
LIMIT $limit
这将仅选择 1 张图片和 picture_votes 中所有记录的计数。
你真的想使用 LEFT join，因为这将返回所有图片，而不仅仅是那些有投票的图片。 你还应该做sum（pv.vote）vs COUNT（），以防你的投票超过1（嘿，它可能会发生！试想一下：高级帐户 == x2 票 ;-）
SELECT p.*, SUM(pv.vote) votes FROM pictures p
LEFT JOIN picture_votes pv
ON pv.picture_id=p.picture_id
GROUP BY pv.picture_id
如果您曾经想按前 10 名的票数排序：
SELECT * FROM (
SELECT p.*, SUM(pv.vote) votes FROM pictures p
LEFT JOIN picture_votes pv
ON pv.picture_id=p.picture_id
GROUP BY pv.picture_id
) AS aggregate
ORDER BY votes DESC
LIMIT 10;
如果在picture_votes表上索引了picture_id，则以下内容可能比联接更快：
SELECT *
, ( SELECT COUNT(*) FROM picture_votes WHERE picture_id = pictures.picture_id )
FROM pictures
这可能会完全跳过表的内容，并允许您简单地计算哈希表中的记录，这应该更快。