4个表连接问题。

[woshizn]

一个表基本信息：jbxx （RID，xm --姓名，bm --编码）
一个表处罚信息：cfxx （RID,wwk，RYID --外键指向RID）
一个表奖励信息：jlxx （RID,sss，RYID --外键指向RID）
一个表重大奖励信息：zdjlxx （RID,wwb，RYID --外键指向RID）

表结构是这样的。如何用一个sql求出下列答案。

xm | bm | count（处罚信息）| count（奖励信息） | count（重大奖励信息）

只搜索出有的人，如果某个人只有1项有，其他2项则为0，以此类推。
前提是不能用 where jbxx.RID = cfxx.RYID or jlxx.RID = cfxx.RYID or zdjlxx.RID = cfxx.RYID
这样执行效率太低了。
怎样才能得到最优化，速度最快的sql呢？

[woshizn]

where jbxx.RID = cfxx.RYID or jlxx.RID = jbxx.RYID or zdjlxx.RID = jbxx.RYID

[shiyiwan]

select xm,bm,
       sum(case when t.flag = 1 then 1 end),
       sum(case when t.flag = 2 then 1 end),
       sum(case when t.flag = 3 then 1 end)
from  jbxx t0,
   (  select ryid, 1 flag from cfxx
      union all
      select ryid, 2 flag from jlxx
      union all
      select ryid, 3 flag from zdjlxx
   )  t
where t0.rid = t.rid
group by t0.xm,t0.bm;

[inthirties]

select a.RID, count(b.RID), count(c.RID), count(d.RID) from jbxx a, cfxx b, jlxx c, zdjlxx d where a.RID=b.RID(+) and a.RID=c.RID(+) and a.RID=c.RID(+) and a.RID=d.RID(+) group by a.RID having count(b.RID)+count(c.RID)+count(d.RID)>0

[inthirties]

shiyiwan的也行。

要想有比较好的性能的话，需要看你的数据是怎样分布的。

[cosio]

数据是怎样分布的!然后还要表是否有建索引?

[bingsha1976]

select jb.xm,jb.bm,temp.wwk,temp.sss,temp.wwb from
(select rid,sum(wwk) as wwk,sum(sss) as sss,sum(wwb) as wwb from
(select c.RYID as rid,count(c.wwk) as wwk,0 as sss,0 as wwb from cfxx c
union
select j.RYID as rid,0 as wwk,count(j.sss) as sss,0 as wwb from jlxx j
union
select z.RYID as rid,0 as wwk,0 as sss,count(z.wwb) as wwb from zdjlxx z)
group by rid) temp,jbxx jb where temp.rid=jbxx.rid

[woshizn]

没有什么特别的分布，就是顺序往下插的。
jbxx大概有3W多条。
其他3个表加起来，大概有1W多条。

[小灰狼W]

这样效率较高吧

select am,bm,
       (select count(1) from cfxx where t.rid=ryid),
       (select count(1) from jlxx where t.rid=ryid),
       (select count(1) from zdjlxx where t.rid=ryid)
from  jbxx t

[小灰狼W]

select xm,bm,
       (select count(1) from cfxx where t.rid=ryid),
       (select count(1) from jlxx where t.rid=ryid),
       (select count(1) from zdjlxx where t.rid=ryid)
from  jbxx t
where exists(select 1 from cfxx where t.rid=ryid)
  or exists(select 1 from jlxx where t.rid=ryid)
  or exists(select 1 from zdjlxx where t.rid=ryid)

[小灰狼W]

[Quote=引用 3 楼 inthirties 的回复:]
select a.RID, count(b.RID), count(c.RID), count(d.RID) from jbxx a, cfxx b, jlxx c, zdjlxx d where a.RID=b.RID(+) and a.RID=c.RID(+) and a.RID=c.RID(+) and a.RID=d.RID(+) group by a.RID having count(b¡­
[/Quote]
这样连接有笛卡尔积的，count里还要加上distinct

[超叔csdn]

where jbxx.RID = cfxx.RYID or jlxx.RID = cfxx.RYID or zdjlxx.RID = cfxx.RYID ?

估计你的表连接条件是：
from jbxx,cfxx,jlxx,zdjlxx
where jbxx.RID = cfxx.RYID or jlxx.RID = cfxx.RYID or zdjlxx.RID = cfxx.RYID
这种连接明显是错误的，明显是你的代码问题，非效率问题。

改成shiyiwan或者inthirties的写法就OK了。