select a.nsr_swjg_dm,
a.nsrzt_dm,--纳税人状态
a.djzclx_dm,
a.hy_dm,
count(distinct a.nsrsbh) --统计不重复的户数,纳税人识别号:nsrsbh
from Dj_Nsrzt_Bg a,RD_NSRZG_LSXX b,dj_sz_ls c
where (a.nsrsbh = b.nsrsbh)
and (a.nsrsbh=c.nsrsbh)
and (a.nsrzt_dl_dm < '5')
and (a.YXQ_Q <= ld_bym)
and (a.YXQ_Z >= ld_byc or a.YXQ_Z is null )
and (b.NSRZG_DM IN('01','02','03'))
and (b.YXQ_Q <= ld_bym)
and (b.YXQ_Z >= ld_byc or b.YXQ_Z is null )
and (c.zsxm_dm='01')
and (c.ZSXMYXQ_Q <= ld_bym)
and (c.ZSXMYXQ_Z >= ld_byc or c.ZSXMYXQ_Z is null)
and (a.nsr_swjg_dm like lvc_qxswjg)
group by a.nsr_swjg_dm,
a.nsrzt_dm,
a.djzclx_dm,
a.hy_dm;
其它的条件正确,中间where条件不用深入研究。问题是:现在统计出了count(distinct a.nsrsbh)一样,但是nsrzt_dm纳税人状态不一样的数据。请问是不是group by这样写不对....
a.nsrzt_dm,--纳税人状态
a.djzclx_dm,
a.hy_dm,
count(distinct a.nsrsbh) --统计不重复的户数,纳税人识别号:nsrsbh
from Dj_Nsrzt_Bg a,RD_NSRZG_LSXX b,dj_sz_ls c
where (a.nsrsbh = b.nsrsbh)
and (a.nsrsbh=c.nsrsbh)
and (a.nsrzt_dl_dm < '5')
and (a.YXQ_Q <= ld_bym)
and (a.YXQ_Z >= ld_byc or a.YXQ_Z is null )
and (b.NSRZG_DM IN('01','02','03'))
and (b.YXQ_Q <= ld_bym)
and (b.YXQ_Z >= ld_byc or b.YXQ_Z is null )
and (c.zsxm_dm='01')
and (c.ZSXMYXQ_Q <= ld_bym)
and (c.ZSXMYXQ_Z >= ld_byc or c.ZSXMYXQ_Z is null)
and (a.nsr_swjg_dm like lvc_qxswjg)
group by a.nsr_swjg_dm,
a.nsrzt_dm,
a.djzclx_dm,
a.hy_dm;
其它的条件正确,中间where条件不用深入研究。问题是:现在统计出了count(distinct a.nsrsbh)一样,但是nsrzt_dm纳税人状态不一样的数据。请问是不是group by这样写不对....
先group by 在distinct count 的 应该就是1
a.nsrzt_dm,--纳税人状态
a.djzclx_dm,
a.hy_dm,
(select count(distinct a.nsrsbh) as number from table_name) --统计不重复的户数,纳税人识别号:nsrsbh
from Dj_Nsrzt_Bg a,RD_NSRZG_LSXX b,dj_sz_ls c
where (a.nsrsbh = b.nsrsbh)
and (a.nsrsbh=c.nsrsbh)
and (a.nsrzt_dl_dm < '5')
and (a.YXQ_Q <= ld_bym)
and (a.YXQ_Z >= ld_byc or a.YXQ_Z is null )
and (b.NSRZG_DM IN('01','02','03'))
and (b.YXQ_Q <= ld_bym)
and (b.YXQ_Z >= ld_byc or b.YXQ_Z is null )
and (c.zsxm_dm='01')
and (c.ZSXMYXQ_Q <= ld_bym)
and (c.ZSXMYXQ_Z >= ld_byc or c.ZSXMYXQ_Z is null)
and (a.nsr_swjg_dm like lvc_qxswjg)
group by a.nsr_swjg_dm,
a.nsrzt_dm,
a.djzclx_dm,
a.hy_dm;
这样改应该符合你的逻辑了
a.nsrzt_dm,--纳税人状态
a.djzclx_dm,
a.hy_dm,
(select count(distinct a.nsrsbh) from table_name) as number
--统计不重复的数,纳税人识别号:nsrsbh
from Dj_Nsrzt_Bg a,RD_NSRZG_LSXX b,dj_sz_ls c
where (a.nsrsbh = b.nsrsbh)
and (a.nsrsbh=c.nsrsbh)
and (a.nsrzt_dl_dm < '5')
and (a.YXQ_Q <= ld_bym)
and (a.YXQ_Z >= ld_byc or a.YXQ_Z is null )
and (b.NSRZG_DM IN('01','02','03'))
and (b.YXQ_Q <= ld_bym)
and (b.YXQ_Z >= ld_byc or b.YXQ_Z is null )
and (c.zsxm_dm='01')
and (c.ZSXMYXQ_Q <= ld_bym)
and (c.ZSXMYXQ_Z >= ld_byc or c.ZSXMYXQ_Z is null)
and (a.nsr_swjg_dm like lvc_qxswjg)
group by a.nsr_swjg_dm,
a.nsrzt_dm,
a.djzclx_dm,
a.hy_dm;
这样实验下
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
Line: 40
Text: --(select count(distinct a.nsrsbh) from table_name) as numberError: PLS-00103: Encountered the symbol "AS" when expecting one of the following:
; return returning and or
Line: 40
Text: --(select count(distinct a.nsrsbh) from table_name) as number
这些都是报出的错误.....select 不能跟group by 一起用,继续研究
按照现有的写法,肯定会出现“count(distinct a.nsrsbh)一样,但是nsrzt_dm纳税人状态不一样的数据”,这个如果翻译为业务就是:不同状态的纳税人的数目完成可以是一样的。
所以,你要别人帮你,就要把问题描述清楚,这样别人可以明白你要什么。