下面是我写的一段oracle语句,虽说功能实现了,但是我觉得有很多重复的代码,可是我不知道怎么优化提高性能,请各位大侠帮我看看,谢谢!select a.datasoure,a.cardname,a.cardvalue,decode(a.storecount-b.count,null,a.storecount,a.storecount-b.count),
decode(c.countissue,null,0,c.countissue),decode(d.countstore,null,0,d.countstore),e.allcount
from
(select t.datasoure datasoure, ty.cardname cardname, t.cardvalue cardvalue, count(*) storecount
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(mt.storetime, 'yyyy-mm-dd HH24:mi:ss') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by t.datasoure, ty.cardname, t.cardvalue )a ,
(select mt.datasoure datasoure, ty.cardname cardname, mt.cardvalue cardvalue,count(*) count
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by mt.datasoure, ty.cardname, mt.cardvalue )b,
(select mt.datasoure datasoure, ty.cardname cardname, mt.cardvalue cardvalue,count(*) countissue
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') = TO_DATE('2010-12-08', 'yyyy-mm-dd') -1
group by mt.datasoure, ty.cardname, mt.cardvalue)c ,
(select t.datasoure datasoure, ty.cardname cardname, t.cardvalue cardvalue, count(*) countstore
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(substr(mt.storetime,1,10), 'yyyy-mm-dd') = TO_DATE('2010-12-08', 'yyyy-mm-dd') -1
group by t.datasoure, ty.cardname, t.cardvalue) d,
(select a.datasoure datasoure,decode(a.storecount-b.count,null,a.storecount,a.storecount-b.count)allcount
from
(select t.datasoure datasoure, count(*) storecount
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(mt.storetime, 'yyyy-mm-dd HH24:mi:ss') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by t.datasoure, ty.cardname, t.cardvalue )a ,
(select mt.datasoure datasoure, count(*) count
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by mt.datasoure, ty.cardname, mt.cardvalue )b
where a.datasoure = b.datasoure(+) )e
where a.datasoure = b.datasoure(+) and a.cardname = b.cardname(+) and a.cardvalue = b.cardvalue(+)
and c.datasoure(+) = a.datasoure and c.cardname(+) = a.cardname and c.cardvalue(+) = a.cardvalue
and d.datasoure(+) = a.datasoure and d.cardname(+) = a.cardname and d.cardvalue(+) = a.cardvalue
and e.datasoure(+) = a.datasoure
decode(c.countissue,null,0,c.countissue),decode(d.countstore,null,0,d.countstore),e.allcount
from
(select t.datasoure datasoure, ty.cardname cardname, t.cardvalue cardvalue, count(*) storecount
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(mt.storetime, 'yyyy-mm-dd HH24:mi:ss') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by t.datasoure, ty.cardname, t.cardvalue )a ,
(select mt.datasoure datasoure, ty.cardname cardname, mt.cardvalue cardvalue,count(*) count
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by mt.datasoure, ty.cardname, mt.cardvalue )b,
(select mt.datasoure datasoure, ty.cardname cardname, mt.cardvalue cardvalue,count(*) countissue
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') = TO_DATE('2010-12-08', 'yyyy-mm-dd') -1
group by mt.datasoure, ty.cardname, mt.cardvalue)c ,
(select t.datasoure datasoure, ty.cardname cardname, t.cardvalue cardvalue, count(*) countstore
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(substr(mt.storetime,1,10), 'yyyy-mm-dd') = TO_DATE('2010-12-08', 'yyyy-mm-dd') -1
group by t.datasoure, ty.cardname, t.cardvalue) d,
(select a.datasoure datasoure,decode(a.storecount-b.count,null,a.storecount,a.storecount-b.count)allcount
from
(select t.datasoure datasoure, count(*) storecount
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(mt.storetime, 'yyyy-mm-dd HH24:mi:ss') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by t.datasoure, ty.cardname, t.cardvalue )a ,
(select mt.datasoure datasoure, count(*) count
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by mt.datasoure, ty.cardname, mt.cardvalue )b
where a.datasoure = b.datasoure(+) )e
where a.datasoure = b.datasoure(+) and a.cardname = b.cardname(+) and a.cardvalue = b.cardvalue(+)
and c.datasoure(+) = a.datasoure and c.cardname(+) = a.cardname and c.cardvalue(+) = a.cardvalue
and d.datasoure(+) = a.datasoure and d.cardname(+) = a.cardname and d.cardvalue(+) = a.cardvalue
and e.datasoure(+) = a.datasoure
怎么子查询里关联的表都是m_cards t, m_cardtype ty , m_store mt
我这段代码是为了查找库存量,用某时间以前的入库数-某时间以前的出库数
select a.datasoure,a.cardname,a.cardvalue,decode(a.storecount-b.count,null,a.storecount,a.storecount-b.count)/*入库数减去出库数*/,
decode(c.countissue,null,0,c.countissue)/*某时间当日的出库数*/,decode(d.countstore,null,0,d.countstore)/*某时间当日的入库数*/,e.allcount/*只按datasoure分组,查询同一datasource的库存量*/
from
(select t.datasoure datasoure, ty.cardname cardname, t.cardvalue cardvalue, count(*) storecount
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(mt.storetime, 'yyyy-mm-dd HH24:mi:ss') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by t.datasoure, ty.cardname, t.cardvalue )a /*入库表查询*/,
(select mt.datasoure datasoure, ty.cardname cardname, mt.cardvalue cardvalue,count(*) count
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by mt.datasoure, ty.cardname, mt.cardvalue )b/*出库表查询*/,
(select mt.datasoure datasoure, ty.cardname cardname, mt.cardvalue cardvalue,count(*) countissue
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') = TO_DATE('2010-12-08', 'yyyy-mm-dd') -1
group by mt.datasoure, ty.cardname, mt.cardvalue)c /*当日的入库表查询*/,
(select t.datasoure datasoure, ty.cardname cardname, t.cardvalue cardvalue, count(*) countstore
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(substr(mt.storetime,1,10), 'yyyy-mm-dd') = TO_DATE('2010-12-08', 'yyyy-mm-dd') -1
group by t.datasoure, ty.cardname, t.cardvalue) d/*当日的出库表查询*/,
(select a.datasoure datasoure,decode(a.storecount-b.count,null,a.storecount,a.storecount-b.count)allcount
from
(select t.datasoure datasoure, count(*) storecount
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(mt.storetime, 'yyyy-mm-dd HH24:mi:ss') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by t.datasoure, ty.cardname, t.cardvalue )a ,
(select mt.datasoure datasoure, count(*) count
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by mt.datasoure, ty.cardname, mt.cardvalue )b
where a.datasoure = b.datasoure(+) )e/*只查询同一datasoure的数量*/
where a.datasoure = b.datasoure(+) and a.cardname = b.cardname(+) and a.cardvalue = b.cardvalue(+)
and c.datasoure(+) = a.datasoure and c.cardname(+) = a.cardname and c.cardvalue(+) = a.cardvalue
and d.datasoure(+) = a.datasoure and d.cardname(+) = a.cardname and d.cardvalue(+) = a.cardvalue
and e.datasoure(+) = a.datasoure
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by mt.datasoure, ty.cardname, mt.cardvalue ;
2、create table a as select t.datasoure datasoure, count(*) storecount
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(mt.storetime, 'yyyy-mm-dd HH24:mi:ss') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by t.datasoure, ty.cardname, t.cardvalue ;3、create table e as select a.datasoure datasoure,decode(a.storecount b.count,null,a.storecount,a.storecount-b.count)allcount
from a,b
where a.datasoure = b.datasoure(+)
同理,把那些子查询的结果保存在临时表中,然后在将临时表连接起来做最后的查询。这样效率会高很多的
1、create table a as select t.datasoure datasoure, count(*) storecount
from m_cards t, m_cardtype ty , m_store mt
where t.cardtype = ty.cardtype and t.issuetag = 0 and t.StoreID = mt.StoreID
AND TO_DATE(mt.storetime, 'yyyy-mm-dd HH24:mi:ss') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by t.datasoure, ty.cardname, t.cardvalue;2、create table b as select mt.datasoure datasoure, count(*) count
from m_cardtype ty , m_Issue mt
where mt.cardtype = ty.cardtype and mt.issuetag = 1
AND TO_DATE(mt.IssueTime, 'yyyy-mm-dd') <= TO_DATE('2010-12-08', 'yyyy-mm-dd')
group by mt.datasoure, ty.cardname, mt.cardvalue ;3、create table e as select a.datasoure datasoure,decode(a.storecount-b.count,null,a.storecount,a.storecount-b.count)allcount
from a,b
where a.datasoure = b.datasoure(+)
同理,你都可以把子查询先拿出来,结果保存在临时表中,最后把临时表连接查询,这样效率会高很多的。
我这oracle语句是页面有个按钮,输入日期后,点击按钮调用后台的此oracle语句,再把查询的信息显示在页面上
再说 你那个输入的日期相当于一个参数,传给sql就行了
需要,你是输入一个日期值,然后点击按钮调用后台的sql,由于每次的数据会变,所以你每次点击按钮后,都要创建临时表;
你可以把这段sql放到一个子方法中,每次点击按钮就调用方法查询,参数就是你输入的日期值;
而方法的内容就是 临时表 + sql查询
int search(Date d){ create table a as select ......;
create table b as select ......;
........
select .... from a,b.... where ....; return ;}