这样的多张表如何合并到一张表里？

[zxz19]

select lp.ID,lp.Title,count(pic.ID)as Pic 
from tb_Loupan lp left join tb_LoupanPics pic on pic.LpId=lp.ID 
group by lp.ID,lp.Title 

 ----======= 
select lp.ID,lp.Title,Count(ihx.HxId)as Huxing 
from tb_Loupan lp left join tb_LoupanInHuxing ihx on ihx.LpId=lp.ID 
group by lp.ID,lp.Title 

 -- ----============ 
select lp.ID,lp.Title,Count(dp.ID)as Dianping 
from tb_Loupan lp left join tb_LoupanDianping dp on dp.LpId=lp.ID 
group by lp.ID,lp.Title 

 ----=================== 
select lp.ID,lp.Title,count(yx.ID)as Yixiang 
from tb_Loupan lp left join tb_LoupanYixiang yx on yx.LpId=lp.ID 
group by lp.ID,lp.Title 

这样四个分别统计查询出来的每个对应的结果分别为：
ID Title Pic
--------------------------------
6 fffffff 0
5 111111111 0
4 ggggggg1 0
3 aaaaa 0

ID Title Hx
------------------------------------------------------
6 fffffff 0
5 111111111 0
4 ggggggg1 0
3 aaaaa 0

ID Title Dp Yx
------------------------------------------------------
6 fffffff 0
5 111111111 3
4 ggggggg1 0
3 aaaaa 2

ID Title Yx
------------------------------------------------------
6 fffffff 0
5 111111111 3
4 ggggggg1 1
3 aaaaa 0

但是这样的结果又该如何如何为这样呢？
ID Title Pic Hx Dp Yx
-----------------------------------
6 fffffff 0 0 0 0
5 111111111 0 0 3 3
4 ggggggg1 0 0 0 1
3 aaaaa 0 0 2 0

[dawugui]

把四个查询做为子查询,然后通过id,连接.

[dawugui]

select t1.id , t1.title , t1.pic , t2.huxing hx , t3.dianping dp , t4.yixiang yx from
(
select lp.ID,lp.Title,count(pic.ID)as Pic 
from tb_Loupan lp left join tb_LoupanPics pic on pic.LpId=lp.ID 
group by lp.ID,lp.Title 
) t1,
(
select lp.ID,lp.Title,Count(ihx.HxId)as Huxing 
from tb_Loupan lp left join tb_LoupanInHuxing ihx on ihx.LpId=lp.ID 
group by lp.ID,lp.Title 
) t2,
(
select lp.ID,lp.Title,Count(dp.ID)as Dianping 
from tb_Loupan lp left join tb_LoupanDianping dp on dp.LpId=lp.ID 
group by lp.ID,lp.Title 
) t3,
(
select lp.ID,lp.Title,count(yx.ID)as Yixiang 
from tb_Loupan lp left join tb_LoupanYixiang yx on yx.LpId=lp.ID 
group by lp.ID,lp.Title 
) t4
where t1.id = t2.id and t1.id = t3.id and t1.id = t4.id

[流年筱澈]

四表联查麻烦,建议 用子查询或者 另外一种联合查询写法
select ID Title Pic Hx Dp Yx from table1,table2,table3,table4
where t1.=t2. and t2.=t3.
where后面连接各自表的主外键

[中国风]

在另一貼已回復了

select lp.ID,lp.Title,
COUNT(DISTINCT pic.ID) as Pic,
COUNT(DISTINCT ihx.HxId) as Hx,
COUNT(DISTINCT dp.ID) as Dp,
COUNT(DISTINCT yx.ID) as Yx
from tb_Loupan lp 
    left join tb_LoupanPics pic on pic.LpId=lp.ID 
    left join tb_LoupanInHuxing ihx on ihx.LpId=lp.ID 
    left join tb_LoupanDianping dp on dp.LpId=lp.ID 
    left join tb_LoupanYixiang yx on yx.LpId=lp.ID
GROUP BY lp.ID,lp.Title,

[--小F--]

;with f as
(
select lp.ID,lp.Title,pic.ID as Pic,ihx.HxId as Hx,dp.ID as Dp,yx.ID as Yx
from tb_Loupan lp 
    left join tb_LoupanPics pic on pic.LpId=lp.ID 
    left join tb_LoupanInHuxing ihx on ihx.LpId=lp.ID 
    left join tb_LoupanDianping dp on dp.LpId=lp.ID 
    left join tb_LoupanYixiang yx on yx.LpId=lp.ID
order by lp.AddTime desc
)

select
  id,title,
  sum(case when pic is not null then 1 else 0 end) as pic , 
  sum(case when Hx  is not null then 1 else 0 end) as Hx , 
  sum(case when Dp  is not null then 1 else 0 end) as Dp , 
  sum(case when Yx  is not null then 1 else 0 end) as Yx 
from 
  f
group by
  id,title

[快溜]

select
  id,title,
  sum(case when pic is not null then 1 else 0 end) as pic , 
  sum(case when Hx  is not null then 1 else 0 end) as Hx , 
  sum(case when Dp  is not null then 1 else 0 end) as Dp , 
  sum(case when Yx  is not null then 1 else 0 end) as Yx 
from 
  (
select lp.ID,lp.Title,pic.ID as Pic,ihx.HxId as Hx,dp.ID as Dp,yx.ID as Yx
from tb_Loupan lp 
    left join tb_LoupanPics pic on pic.LpId=lp.ID 
    left join tb_LoupanInHuxing ihx on ihx.LpId=lp.ID 
    left join tb_LoupanDianping dp on dp.LpId=lp.ID 
    left join tb_LoupanYixiang yx on yx.LpId=lp.ID
order by lp.AddTime desc
) t

group by
  id,title

[zxz19]

爱新觉罗.毓华和中國風的解答是可以的，
小F和123的回答有点小问题我在另外一帖里也说了，这样查询出来的统计数有被重复统计的，另外这里我再说下这个查询里，order by不能写在子查询里面，会报错

最后谢谢大家的热心帮助！