一个表基本信息: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呢?
一个表处罚信息: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呢?
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;
(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
jbxx大概有3W多条。
其他3个表加起来,大概有1W多条。
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
(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)
from jbxx,cfxx,jlxx,zdjlxx
where jbxx.RID = cfxx.RYID or jlxx.RID = cfxx.RYID or zdjlxx.RID = cfxx.RYID
这种连接明显是错误的,明显是你的代码问题,非效率问题。改成shiyiwan或者inthirties的写法就OK了。