现有大概这样一个查询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
查询出结果为:
ID Title Pic Hx Dp Yx
------------------------------------------------------
6 fffffff NULL NULL NULL NULL
5 111111111 NULL NULL 3 2
5 111111111 NULL NULL 3 3
5 111111111 NULL NULL 3 4
5 111111111 NULL NULL 4 2
5 111111111 NULL NULL 4 3
5 111111111 NULL NULL 4 4
5 111111111 NULL NULL 5 2
5 111111111 NULL NULL 5 3
5 111111111 NULL NULL 5 4
4 ggggggg1 NULL NULL NULL 1
3 aaaaa NULL NULL 1 NULL
3 aaaaa NULL NULL 2 NULL------------------------------------------------------------
如何对后面四项进行汇总统计,分别统计出每条信息的pic有几张,hx有几种,dp有几条,yx有几条
我想要的结果是这样: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
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
查询出结果为:
ID Title Pic Hx Dp Yx
------------------------------------------------------
6 fffffff NULL NULL NULL NULL
5 111111111 NULL NULL 3 2
5 111111111 NULL NULL 3 3
5 111111111 NULL NULL 3 4
5 111111111 NULL NULL 4 2
5 111111111 NULL NULL 4 3
5 111111111 NULL NULL 4 4
5 111111111 NULL NULL 5 2
5 111111111 NULL NULL 5 3
5 111111111 NULL NULL 5 4
4 ggggggg1 NULL NULL NULL 1
3 aaaaa NULL NULL 1 NULL
3 aaaaa NULL NULL 2 NULL------------------------------------------------------------
如何对后面四项进行汇总统计,分别统计出每条信息的pic有几张,hx有几种,dp有几条,yx有几条
我想要的结果是这样: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
sum(case when pic is not null then 1 else 0 end) pic ,
sum(case when Hx is not null then 1 else 0 end) Hx ,
sum(case when Dp is not null then 1 else 0 end) Dp ,
sum(case when Yx is not null then 1 else 0 end) 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
) t
group by lp.ID,lp.Title
select id , titile ,
sum(case when pic is not null then 1 else 0 end) pic ,
sum(case when Hx is not null then 1 else 0 end) Hx ,
sum(case when Dp is not null then 1 else 0 end) Dp ,
sum(case when Yx is not null then 1 else 0 end) 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
) t
group by ID,Title
COUNT(pic.ID) as Pic,
COUNT(ihx.HxId) as Hx,
COUNT(dp.ID) as Dp,
COUNT(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,
(
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
这样跟我开始用的查询方法效果是一样的,就是会重复统计了,比如上面id为5 的信息,Dp和Yx统计出来都是9而不是3了
楼盘信息表:tb_Loupan(ID,Title,...等字段)
图库信息表:tb_LoupanPic(ID,LpId,...)
户型信息表: tb_LoupanHuxing(ID,Area,....)
楼盘与户型关联信息:tb_LoupanInHuxing(HxId,LpId)
点评记录: tb_LoupanDianping(ID,LpId,....)
购买意向记录:tb_LoupanYixiang(ID,LpId,...)其中所有从表中LpId对应tb_Loupan的ID,HxId对应tb_Huxing的ID现在是要查询楼盘信息的列表,列出楼盘的基本信息,后面对应每个楼盘有多少图片,多少户型,点评和购买意向
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
order by lp.AddTime desc----=======
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
order by lp.AddTime desc
--
----============
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
order by lp.AddTime desc----===================
select lp.ID,lp.Title,count(yx.ID)as Yixiang
from tb_Loupan lp
left join tb_LoupanYixiang yx on yx.LpId=lp.IDgroup by lp.ID,lp.Title
order by lp.AddTime desc
如果是这样四个分别统计查询出来的每个对应的结果就是正确的:
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 2ID 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
這樣改改
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,