select a.a_guid, p.p_img as a_cover,count(p.a_guid) a_count
from t_album a
left join t_photo p on a.a_guid=p.a_guid
where uname='abc'
group by a.a_guid,p.p_img
from t_album a
left join t_photo p on a.a_guid=p.a_guid
where uname='abc'
group by a.a_guid,p.p_img
t_album 是封面表,封面对应的图片应该在这个表里面啊,你的p_img是在这个表里面吗?如果不在这个表里面,而是在t_photo里面,那么你怎么区分是封面图片还是相片呢?
--try:
select a.a_guid,b.p_img,a.a_count from
(select a.a_guid,a_cover,count(p.a_guid) a_count
from t_album a
left join t_photo p on a.a_guid=p.a_guid
where uname='abc'
group by a.a_guid,a.a_cover)a
left join t_photo b
on a.a_cover=b.a_guid
select a.a_guid,a_cover=b.p_img,count(p.a_guid) a_count
from t_album a
left join t_photo p on a.a_guid=p.a_guid
where uname='abc'
group by a.a_guid,b.p_img
a_cover 是相册表t_album里的一个字段。。它就是t_photo表里的那个a_guid
示例: a_guid a_cover a_count
a 1 2
b 2 3 目标效果:
a_guid a_img a_count
a ../aa.jpg 2
b ../aa.jpg 3 表结构如下 :
t_album :a_guid, a_cover
t_photo:p_guid,p_img
a_cover和p_guid一样的
select a.a_guid,a_cover=(select p_img from t_photo where id = a.a_cover and a_guid = b.a-guid)
,count(p.a_guid) a_count
from t_album a
left join t_photo p on a.a_guid=p.a_guid
where uname='abc'
group by a.a_guid,a.a_cover