日期:2014-05-17  浏览次数:20854 次

一个很蛋疼的sql,性能太差求优化
具体的sql就不写了, 太长了,

需求是这样的:
  
  一个历史表 里面几个关键的字段 一个主键id 公司标识copmanyid 一个成功或失败状态 status 一个createtime 需求很简单  

现在要查询下面的数据:
  1 如果 某公司只有一条历史记录,则显示状态为失败的、
  2 如果 公司有多条记录,只要有一条是成功的就不显示,如果都为失败的,则显示最新及最近的一条


我的思路是这样的 在java程序中作判断 然后拼接sql, 多条记录union 一条记录,一条记录的好搞,主要是多条记录的,首先查出多条记录并且全部为失败的公司标识,然后没查出一个公司标识就union一次, 结果表里数据很多,union了几十次,直接卡死,


哪位大侠给个指点 ,实在想不出好的方法了,在sql优化或者在java程序中处理都可以!!!!

分不多了,全部献上...

------解决方案--------------------
SQL code

--此处假设status为0是失败,1成功
select companyid,max(createtime) d
    from 你的表
    group by companyid
    having (count(1)>1 and count(decode(status,0,null,1))=0)
        or (count(1)=1);

------解决方案--------------------
记得在companyid列建立索引
------解决方案--------------------
是要一次性,所有公司都查出来,还是一个公司 一个公司的查?

如果是所有公司都查出来,建议走后台 存储过程。

把所有逻辑写到后台PRC里,计算出来后,前台JAVA只管查结果表就好了。
------解决方案--------------------
建表语句,数据准备,能不能发出来?
老是回答者建表,搞数据,麻烦啊。
。。。。。。
------解决方案--------------------
探讨

这个表有其他很多字段,我我要展示所有字段的 .......

------解决方案--------------------
这个可以直接写一个存储过程来解决,

SQL code

create or replace procedure proc_history
h_id in number,--输入某公司的ID
is
h_countid integer;
h_countstate integer;
begin
select count(*) into h_countid from Table_history where id=h_id;
if h_countid=1 then
select * from Table_history where status=0 and id=h_id;--显示状态为失败(0)的数据
end if;

select count(*) into h_countstate from Table_history where status=1;
if h_countstate=0 then
select * from (select * from Table_history order by createtime desc) where rownum=1
end if;
end;

------解决方案--------------------
SQL code

-- 0:success 1:failed
WITH tb AS (
     SELECT 1 tid,'c1' copmanyid,0 status,TO_DATE('2011-04-01 15:10:42','yyyy-mm-dd hh24:mi:ss') createtime FROM DUAL UNION ALL
     SELECT 2 tid,'c2' copmanyid,1 status,TO_DATE('2011-04-02 15:10:42','yyyy-mm-dd hh24:mi:ss') createtime FROM DUAL UNION ALL
     SELECT 3 tid,'c3' copmanyid,1 status,TO_DATE('2011-04-03 15:10:42','yyyy-mm-dd hh24:mi:ss') createtime FROM DUAL UNION ALL
     SELECT 4 tid,'c3' copmanyid,0 status,TO_DATE('2011-04-04 15:10:42','yyyy-mm-dd hh24:mi:ss') createtime FROM DUAL UNION ALL
     SELECT 5 tid,'c3' copmanyid,1 status,TO_DATE('2011-04-05 15:10:42','yyyy-mm-dd hh24:mi:ss') createtime FROM DUAL UNION ALL
     SELECT 6 tid,'c4' copmanyid,1 status,TO_DATE('2011-04-08 15:10:42','yyyy-mm-dd hh24:mi:ss') createtime FROM DUAL UNION ALL
     SELECT 7 tid,'c4' copmanyid,1 status,TO_DATE('2011-04-07 15:10:42','yyyy-mm-dd hh24:mi:ss') createtime FROM DUAL UNION ALL
     SELECT 8 tid,'c4' copmanyid,1 status,TO_DATE('2011-04-09 15:10:42','yyyy-mm-dd hh24:mi:ss') createtime FROM DUAL
)
SELECT n.tid,
       n.copmanyid,
       n.status,
       n.createtime
  FROM (SELECT m.tid,
               m.copmanyid,
               m.status,
               m.createtime,
               ROW_NUMBER() OVER(PARTITION BY m.copmanyid ORDER BY m.createtime DESC, m.tid ASC) rn
          FROM (SELECT t.*,
                       COUNT(*) OVER(PARTITION BY t.copmanyid) total_cnt,
                       SUM(DECODE(t.status, 0, 1, 0)) OVER(PARTITION BY t.copmanyid) success_total_cnt
                  FROM tb t) m
         WHERE (m.total_cnt = 1 AND m.status = 1)
            OR (m.total_cnt > 1 AND m.success_total_cnt = 0)) n
 WHERE n.rn <= 2;

       TID COPMANYID     STATUS CREATETIME
---------- --------- ---------- -----------
         2 c2                 1 2011/04/02
         8 c4                 1 2011/04/09
         6 c4                 1 2011/04/08