删掉了许多sum,不小心 from 也删掉了:方式1select to_char(dhz_date,'yyyy-mm-dd') as dhz_date ,(select count(distinct vhz_id) from c_receipt where vyx_flag ='01' and to_char(dhz_date,'yyyy-mm-dd') = to_char( DSK_TIME,'yyyy-mm-dd'))) as NSJ_COUNT ,sum(ntotal_fee) ntotal_fee ,sum(NXY_FEE) nxy_fee from sometable where DHz_date between to_Date('2005-1-31','YYYY-MM-DD hh24:mi:ss') and to_date('2005-3-1','yyyy-mm-dd hh24:mi:ss') group by to_char(dhz_date,'yyyy-mm-dd') / 不是 GROUP BY 表达式方式2select to_char(dhz_date,'yyyy-mm-dd') as dhz_date ,count((select distinct vhz_id from c_receipt where vyx_flag ='01' and to_char(dhz_date,'yyyy-mm-dd') = to_char( DSK_TIME,'yyyy-mm-dd'))) as NSJ_COUNT ,sum(ntotal_fee) ntotal_fee ,sum(NXY_FEE) nxy_fee from sometable where DHz_date between to_Date('2005-1-31','YYYY-MM-DD hh24:mi:ss') and to_date('2005-3-1','yyyy-mm-dd hh24:mi:ss') group by to_char(dhz_date,'yyyy-mm-dd') /NSJ_COUNT 数据被成倍汇总 ,如何解决。此sql 是程序补救措施,否则不会这样写 多谢!
其中 select count(distinct vhz_id) from c_receipt where vyx_flag ='01' and to_char(dhz_date,'yyyy-mm-dd') = to_char( DSK_TIME,'yyyy-mm-dd'))) as NSJ_COUNT 中的 dhz_date 属于 sometable,子句中其他子段 属于表 c_receipt
前面不变将group by to_char(dhz_date,'yyyy-mm-dd')改为group by dhz_date 试试
select dhz_date,count(vhz_id) NSJ_COUNT, sum(ntotal_fee) ntotal_fee, sum(nxy_fee) nxy_fee from ( select to_char(dhz_date,'yyyy-mm-dd') as dhz_date, ( select distinct vhz_id from c_receipt where vyx_flag ='01' and to_char(dhz_date,'yyyy-mm-dd') = to_char( DSK_TIME,'yyyy-mm-dd') ) vhz_id, ntotal_fee, NXY_FEE from sometable where DHz_date between to_Date('2005-1-31','YYYY-MM-DD hh24:mi:ss') and to_date('2005-3-1','yyyy-mm-dd hh24:mi:ss') ) group by dhz_date /
select to_char(dhz_date,'yyyy-mm-dd') dhz_date, nsj_count, sum(ntotal_fee) ntotal_fee, sum(nxy_fee) nxy_fee from sometable a, (select trunc(dsk_time) dsk_time,count(*) nsj_count, from c_receipt where vyx_flag ='01' group by trunc(dsk_time)) b where to_char(dhz_date,'yyyy-mm-dd') = to_char(dsk_time,'yyyy-mm-dd') and dhz_date between to_Date('2005-1-31','YYYY-MM-DD hh24:mi:ss') and to_date('2005-3-1','yyyy-mm-dd hh24:mi:ss') group by to_char(dhz_date,'yyyy-mm-dd'),nsj_count
,(select count(distinct vhz_id)
from c_receipt
where vyx_flag ='01'
and to_char(dhz_date,'yyyy-mm-dd') = to_char( DSK_TIME,'yyyy-mm-dd')))
as NSJ_COUNT
,sum(ntotal_fee) ntotal_fee
,sum(NXY_FEE) nxy_fee
from sometable
where DHz_date between to_Date('2005-1-31','YYYY-MM-DD hh24:mi:ss')
and to_date('2005-3-1','yyyy-mm-dd hh24:mi:ss')
group by to_char(dhz_date,'yyyy-mm-dd')
/
不是 GROUP BY 表达式方式2select to_char(dhz_date,'yyyy-mm-dd') as dhz_date
,count((select distinct vhz_id
from c_receipt
where vyx_flag ='01'
and to_char(dhz_date,'yyyy-mm-dd') = to_char( DSK_TIME,'yyyy-mm-dd')))
as NSJ_COUNT
,sum(ntotal_fee) ntotal_fee
,sum(NXY_FEE) nxy_fee
from sometable
where DHz_date between to_Date('2005-1-31','YYYY-MM-DD hh24:mi:ss')
and to_date('2005-3-1','yyyy-mm-dd hh24:mi:ss')
group by to_char(dhz_date,'yyyy-mm-dd')
/NSJ_COUNT 数据被成倍汇总 ,如何解决。此sql 是程序补救措施,否则不会这样写
多谢!
其中 select count(distinct vhz_id)
from c_receipt
where vyx_flag ='01'
and to_char(dhz_date,'yyyy-mm-dd') = to_char( DSK_TIME,'yyyy-mm-dd')))
as NSJ_COUNT
中的 dhz_date 属于 sometable,子句中其他子段 属于表 c_receipt
sum(ntotal_fee) ntotal_fee,
sum(nxy_fee) nxy_fee
from
(
select to_char(dhz_date,'yyyy-mm-dd') as dhz_date,
(
select distinct vhz_id
from c_receipt
where vyx_flag ='01'
and to_char(dhz_date,'yyyy-mm-dd') = to_char( DSK_TIME,'yyyy-mm-dd')
) vhz_id,
ntotal_fee,
NXY_FEE
from sometable
where DHz_date between to_Date('2005-1-31','YYYY-MM-DD hh24:mi:ss')
and to_date('2005-3-1','yyyy-mm-dd hh24:mi:ss')
)
group by dhz_date
/
nsj_count,
sum(ntotal_fee) ntotal_fee,
sum(nxy_fee) nxy_fee
from sometable a,
(select trunc(dsk_time) dsk_time,count(*) nsj_count,
from c_receipt
where vyx_flag ='01'
group by trunc(dsk_time)) b
where to_char(dhz_date,'yyyy-mm-dd') = to_char(dsk_time,'yyyy-mm-dd')
and dhz_date between to_Date('2005-1-31','YYYY-MM-DD hh24:mi:ss')
and to_date('2005-3-1','yyyy-mm-dd hh24:mi:ss')
group by to_char(dhz_date,'yyyy-mm-dd'),nsj_count