请教 ,关于 GROUP BY

ibmlihw 2005-02-01 12:08:49
如下:

方式1

select 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
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 表达式

方式2

select 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
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 是程序补救措施,否则不会这样写
多谢!
...全文
174 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
ibmlihw 2005-02-01
  • 打赏
  • 举报
回复

其中 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
ORARichard 2005-02-01
  • 打赏
  • 举报
回复
这里出现过的字段名分属哪张表,说明一下
ibmlihw 2005-02-01
  • 打赏
  • 举报
回复
删掉了许多sum,不小心 from 也删掉了:

方式1

select 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 表达式

方式2

select 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 是程序补救措施,否则不会这样写
多谢!
ORARichard 2005-02-01
  • 打赏
  • 举报
回复
你where 前面都没有表或视图,不知道你是怎么查询出结果来的
onejune4450 2005-02-01
  • 打赏
  • 举报
回复
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
ORARichard 2005-02-01
  • 打赏
  • 举报
回复
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
/
ashg_16700 2005-02-01
  • 打赏
  • 举报
回复
前面不变将group by to_char(dhz_date,'yyyy-mm-dd')改为group by dhz_date 试试

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧