脚本宝典收集整理的这篇文章主要介绍了php – 添加一个表来查询和计算数据SQL，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。
我的MySQL表结构
id username
id user_id image
user_follow
id user_id follow_id
id user_id image_id text
$sql = "SELECT u.username as user,i.image as user_image,p.image,p.date From users u LEFT JOIN user_follow f ON u.id = f.follow_id LEFT JOIN images p ON p.user_id = u.id LEFT JOIN images i ON i.id = (SELECT b.id From images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1) WHERE f.user_id = 3 OR p.user_id = 3 ORDER BY p.date DESC";
此行返回用户当前图像(最后一张图像)
LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1)
它返回我和我的朋友的所有图像
[0] => Array
(
[user] => 8888
[user_image] => second.jpg
[image] => second.jpg
[date] => 2012-01-24 14:42:27
)
[1] => Array
(
[user] => 8888
[user_image] => second.jpg
[image] => First.jpg
[date] => 2012-01-24 14:42:27
)
[2] => Array
(
[user] => 3333
[user_image] => ax46l7v7vugnesk10whk_339.jpg
[image] => ax46l7v7vugnesk10whk_339.jpg
[date] => 2012-01-24 01:54:19
)
[3] => Array
(
[user] => 3333
[user_image] => ax46l7v7vugnesk10whk_339.jpg
[image] => aaaaaaaa.jpg
[date] => 2012-01-24 01:49:57
)
我试着补充一下
left join commentaries c ON c.user_id = u.id
结果是
[2] => Array
(
[user] => 3333
[user_image] => ax46l7v7vugnesk10whk_339.jpg
[image] => ax46l7v7vugnesk10whk_339.jpg
[date] => 2012-01-24 01:54:19
[id] => 1
)
[3] => Array
(
[user] => 3333
[user_image] => ax46l7v7vugnesk10whk_339.jpg
[image] => ax46l7v7vugnesk10whk_339.jpg
[date] => 2012-01-24 01:54:19
[id] => 2
)
[4] => Array
(
[user] => 3333
[user_image] => ax46l7v7vugnesk10whk_339.jpg
[image] => aaaaaaaa.jpg
[date] => 2012-01-24 01:49:57
[id] => 1
)
[5] => Array
(
[user] => 3333
[user_image] => ax46l7v7vugnesk10whk_339.jpg
[image] => aaaaaaaa.jpg
[date] => 2012-01-24 01:49:57
[id] => 2
)
SELECT u.username as user,p.date,COALESCE ( imgcount.cnt,0 ) as comments FROM users u LEFT JOIN user_follow f ON u.id = f.follow_id LEFT JOIN images p ON p.user_id = u.id LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1) LEFT JOIN ( SELECT image_id,COUNT(*) as cnt FROM commentaries GROUP BY image_id ) imgcount ON p.id = imgcount.image_id WHERE f.user_id = 3 OR p.user_id = 3 ORDER BY p.date DESC
以上是脚本宝典为你收集整理的php – 添加一个表来查询和计算数据SQL全部内容，希望文章能够帮你解决php – 添加一个表来查询和计算数据SQL所遇到的问题。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。