做了一个SNS的小网站,想问一下,如何用最高效的方法获取多个数据库的数据。比如
album
album_id user_id user_name user_ico album_name album_describe album_cover photo_num add_time update_time blog
blog_id user_id user_name user_ico blog_title blog_content add_time update_time blog_comment
blog_comment_id blog_id user_id user_name comment_content update_time
这三个表,如何用一句sql语句获得所有数据,用以在新鲜事和个人首页中显示出来。还有,怎样统计blog和album的总数量啊。谢谢各位高手
album
album_id user_id user_name user_ico album_name album_describe album_cover photo_num add_time update_time blog
blog_id user_id user_name user_ico blog_title blog_content add_time update_time blog_comment
blog_comment_id blog_id user_id user_name comment_content update_time
这三个表,如何用一句sql语句获得所有数据,用以在新鲜事和个人首页中显示出来。还有,怎样统计blog和album的总数量啊。谢谢各位高手
统计总量直接用count 结合 group by 就行了
我曾经写过一个"SELECT album_id,blog_id FROM aged_album AS a,aged_blog AS b WHERE (a.user_id = '$user_id') OR(b.user_id = '$user_id')",但老是提示WHERE CALUSE
如果可以的话,在用户表中增加是否开通了blog,上传了相片,发表的评论、日志等字段。
性能你实际测试一下select *
from
(
(
select album_id as item_id,
user_name as username,
update_time as updatetime,
'album' as type
from album
order by updatetime
limit 10
)
union
(
select blog_id as item_id,
user_name as username,
update_time as updatetime,
'blog' as type'
from blog
order by updatetime
limit 10
)
union
(
select blog_comment_id as item_id,
user_name as username,
update_time as updatetime,
'comment' as type
from blog_comment
order by updatetime
limit 10
)) tmp
order by updatetime desc
limit 10
;
这个用union和你独立3次查询实际是一样的,不会对效率有太大影响
只是一个在mysql里完成合并和合并后排序的过程,一个在php里完成,
是的,另外你自己写的 "SELECT album_id,blog_id FROM aged_album AS a,aged_blog AS b WHERE (a.user_id = '$user_id') OR(b.user_id = '$user_id')",
是有误的,a表和b表都没有关联起来呢。from两个表,但这两个表没有关联哦。
A.user_id,
A.album_name,
B.blog_title,
C.comment_content
FROM
A 表 A
ON
LEFT JOIN B表 B ON B.ID = A.ID
LEFT JOIN C表 C ON C。ID = A.ID有条件的再加行:
WHERE ..........
排序(如A表的ID由大到小排):
ORDER BY A.ID DESC