试试,在acct_item_code字段上建立索引

解决方案 »

  1.   

    select
        user_id,
        data_process_date,
        jbf    = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end),
        shf    = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end),
        mythf  = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end),
        dxx    = sum(case when acct_item_code in(4,132,77) then receive_fee end,
        ctthf  = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end),
        cxtx   = sum(case when acct_item_code = 34 then receive_fee end),
        wapf   = sum(case when acct_item_code = 9  then receive_fee end),
        ldxs   = sum(case when acct_item_code = 35 then receive_fee end),
        sxf    = sum(case when acct_item_code = 11 then receive_fee end),
        yyzx   = sum(case when acct_item_code = 32 then receive_fee end),
        cl     = sum(case when acct_item_code = 71 then receive_fee end),
        hjzy   = sum(case when acct_item_code = 0  then receive_fee end),
        f12530 = sum(case when acct_item_code = 0  then receive_fee end),
        qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end),
        qqh    = sum(case when acct_item_code = 12 then receive_fee end),
        qprsf  = sum(case when acct_item_code = 13 then receive_fee end),
        yxhd   = sum(case when acct_item_code = 33 then receive_fee end),
        mxf    = sum(case when acct_item_code = 16 then receive_fee end),
        xxf    = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end),
        f12582 = sum(case when acct_item_code = 57 then receive_fee end),
        qtf    = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end)
    from
        BN_Acc_Billdetail
    group by
        a.user_id,a.data_process_date
      

  2.   

    select
        user_id,
        data_process_date,
        jbf    = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end),
        shf    = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end),
        mythf  = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end),
        dxx    = sum(case when acct_item_code in(4,132,77) then receive_fee end,
        ctthf  = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end),
        cxtx   = sum(case when acct_item_code = 34 then receive_fee end),
        wapf   = sum(case when acct_item_code = 9  then receive_fee end),
        ldxs   = sum(case when acct_item_code = 35 then receive_fee end),
        sxf    = sum(case when acct_item_code = 11 then receive_fee end),
        yyzx   = sum(case when acct_item_code = 32 then receive_fee end),
        cl     = sum(case when acct_item_code = 71 then receive_fee end),
        hjzy   = sum(case when acct_item_code = 0  then receive_fee end),
        f12530 = sum(case when acct_item_code = 0  then receive_fee end),
        qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end),
        qqh    = sum(case when acct_item_code = 12 then receive_fee end),
        qprsf  = sum(case when acct_item_code = 13 then receive_fee end),
        yxhd   = sum(case when acct_item_code = 33 then receive_fee end),
        mxf    = sum(case when acct_item_code = 16 then receive_fee end),
        xxf    = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end),
        f12582 = sum(case when acct_item_code = 57 then receive_fee end),
        qtf    = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end)
    from
        BN_Acc_Billdetail
    group by
        user_id,data_process_date
      

  3.   

    我是来拜 libin_ftsafe(子陌红尘) 的,牛人.....另外建议在这四个栏位上照如下顺序建一个组合索引:
    user_id, data_process_date, acct_item_code, receive_fee
      

  4.   

    呵呵!我也是来仰望几位的!>>:)
      

  5.   

    测试不过,:(,我很菜,请笑我。
    我把表的结构贴出来
    BN_Acc_Billdetail表:
     acct_item_code   receive_fee   user_id  late_fee_balance_date
    1                     324.8    123       2005-6-31
    2                     342      123       2005-6-31
    3                     4423     123       2005-6-31
    1                     23       234       2005-6-31
    expenselist表:user_id(varchar(16))user_time(datetime(8))jbf,shf,mythf,dxx,ctthf,cxtx,wapf,ldxs,sxf,yyzx,cl,hjzy,f12530,qtzzyw,qqh,gprsf,yxhd,mwf,xxf,f12582,qtf这些都是money(8)
      

  6.   

    create table #BN_Acc_Billdetail(acct_item_code int,receive_fee money,user_id int,late_fee_balance_date datetime)
    insert into #BN_Acc_Billdetail select 1,324.8,123,'2005-6-30'
    insert into #BN_Acc_Billdetail select 2,342  ,123,'2005-6-30'
    insert into #BN_Acc_Billdetail select 3,4423 ,123,'2005-6-30'
    insert into #BN_Acc_Billdetail select 1,23   ,234,'2005-6-30'select
        user_id,
        late_fee_balance_date,
        jbf    = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end),
        shf    = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end),
        mythf  = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end),
        dxx    = sum(case when acct_item_code in(4,132,77) then receive_fee end),
        ctthf  = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end),
        cxtx   = sum(case when acct_item_code = 34 then receive_fee end),
        wapf   = sum(case when acct_item_code = 9  then receive_fee end),
        ldxs   = sum(case when acct_item_code = 35 then receive_fee end),
        sxf    = sum(case when acct_item_code = 11 then receive_fee end),
        yyzx   = sum(case when acct_item_code = 32 then receive_fee end),
        cl     = sum(case when acct_item_code = 71 then receive_fee end),
        hjzy   = sum(case when acct_item_code = 0  then receive_fee end),
        f12530 = sum(case when acct_item_code = 0  then receive_fee end),
        qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end),
        qqh    = sum(case when acct_item_code = 12 then receive_fee end),
        qprsf  = sum(case when acct_item_code = 13 then receive_fee end),
        yxhd   = sum(case when acct_item_code = 33 then receive_fee end),
        mxf    = sum(case when acct_item_code = 16 then receive_fee end),
        xxf    = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end),
        f12582 = sum(case when acct_item_code = 57 then receive_fee end),
        qtf    = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end)
    from
        #BN_Acc_Billdetail
    group by
        user_id,late_fee_balance_date
      

  7.   

    BTW : 6 月份哪来的 31 号?
      

  8.   

    现在查询执行1分53秒,libin_ftsafe(子陌红尘)真乃神人也,失误的31号,乌龙了.:(
      

  9.   

    现在完整操作时间2分29秒,只是一个月的数据,大家\特别是libin_ftsafe(子陌红尘),觉得还有缩短时间的可能吗?