分组计数

[qq_36934345]

select jysj,jgh,count(*) jyje from cxjy_ls 
   where jgh in
   (select newjgh from xt_jghdzb 
         where   connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) 
          START WITH newjgh='53000000'  CONNECT BY PRIOR newjgh=sjjgh)
    and jysj>='2017-01-05 00:00:00' and jysj<='2017-02-10 23:59:59'
     and jydm in('000001','000001','000006','000002','000001') and jymc is not null  
     group by jysj,jgh ;

我得到的都是行，我想变成下图这样的形式，采用行列结构，然后计数我好像也写错了

[qq_36934345]

引用 11 楼 js14982 的回复:

select substr(jysj,1,10),jgh,count(skh) skh,count(jyje) jyje from cxjy_ls 
   where jgh in
   (select newjgh from xt_jghdzb 
         where   connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) 
          START WITH newjgh='53000000'  CONNECT BY PRIOR newjgh=sjjgh)
    and jysj>='2017-01-05 00:00:00' and jysj<='2017-02-10 23:59:59'
     and jydm in('000001','000001','000006','000002','000001') and jymc is not null  
     group by substr(jysj,1,10),jgh ;

谢了，我将第二个count改成sum就对了，可是还发现了一个问题就是我还想输出个字段JZ但是这个字段在cxjy_ls表中没有，在xt_gyb表中才有，而且字段JZ和JGH还要匹配，这两个字段在cxjy_ls中都有，这个怎么输出呀

[js14982]

count是计数，sum是求和。 输出jz用两表关联就好了

select substr(jysj,1,10),jgh,jz,count(skh) skh,count(jyje) jyje from cxjy_ls a,xt_gyb b
   where a.JGH=b.JGH 
and a.jgh in
   (select newjgh from xt_jghdzb 
         where   connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) 
          START WITH newjgh='53000000'  CONNECT BY PRIOR newjgh=sjjgh)
    and jysj>='2017-01-05 00:00:00' and jysj<='2017-02-10 23:59:59'
     and jydm in('000001','000001','000006','000002','000001') and jymc is not null  
     group by substr(jysj,1,10),jgh ,jz;

按你描述的是这样，不过具体数据要具体分析的

[js14982]

报错了你要指定是a表还是b表，比如你两个表都有jgh这个字段，你要指定是哪个表的，上面语句你自己改一下

[js14982]

select to_char(jysj,'yyyy-mm-dd'),
       count(case when jgh='43004894' then jyje end)  "43004894" ,
       count(case when jgh='43004994' then jyje end)  "43004994" ,
       count(case when jgh='43005003' then jyje end)  "43005003" ,
from cxjy_ls 
   where jgh in
   (select newjgh from xt_jghdzb 
         where   connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) 
          START WITH newjgh='53000000'  CONNECT BY PRIOR newjgh=sjjgh)
    and jysj>='2017-01-05 00:00:00' and jysj<='2017-02-10 23:59:59'
     and jydm in('000001','000001','000006','000002','000001') and jymc is not null 
group by to_char(jysj,'yyyy-mm-dd');

[js14982]

select to_char(jysj,'yyyy-mm-dd'), count(case when jgh='43004894' then jyje end) "43004894" , count(case when jgh='43004994' then jyje end) "43004994" , count(case when jgh='43005003' then jyje end) "43005003" --多了个逗号，去掉 from cxjy_ls where jgh in (select newjgh from xt_jghdzb where connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) START WITH newjgh='53000000' CONNECT BY PRIOR newjgh=sjjgh) and jysj>='2017-01-05 00:00:00' and jysj<='2017-02-10 23:59:59' and jydm in('000001','000001','000006','000002','000001') and jymc is not null group by to_char(jysj,'yyyy-mm-dd');

[qq_36934345]

引用 1 楼 js14982 的回复:

select to_char(jysj,'yyyy-mm-dd'),
       count(case when jgh='43004894' then jyje end)  "43004894" ,
       count(case when jgh='43004994' then jyje end)  "43004994" ,
       count(case when jgh='43005003' then jyje end)  "43005003" ,
from cxjy_ls 
   where jgh in
   (select newjgh from xt_jghdzb 
         where   connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) 
          START WITH newjgh='53000000'  CONNECT BY PRIOR newjgh=sjjgh)
    and jysj>='2017-01-05 00:00:00' and jysj<='2017-02-10 23:59:59'
     and jydm in('000001','000001','000006','000002','000001') and jymc is not null 
group by to_char(jysj,'yyyy-mm-dd');

我那jgh不仅仅只有那三个啊，取得的是这个select语句中所有的jgh (select newjgh from xt_jghdzb where connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) START WITH newjgh='53000000' CONNECT BY PRIOR newjgh=sjjgh)

[qq_36934345]

引用 1 楼 js14982 的回复:

select to_char(jysj,'yyyy-mm-dd'),
       count(case when jgh='43004894' then jyje end)  "43004894" ,
       count(case when jgh='43004994' then jyje end)  "43004994" ,
       count(case when jgh='43005003' then jyje end)  "43005003" ,
from cxjy_ls 
   where jgh in
   (select newjgh from xt_jghdzb 
         where   connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) 
          START WITH newjgh='53000000'  CONNECT BY PRIOR newjgh=sjjgh)
    and jysj>='2017-01-05 00:00:00' and jysj<='2017-02-10 23:59:59'
     and jydm in('000001','000001','000006','000002','000001') and jymc is not null 
group by to_char(jysj,'yyyy-mm-dd');

无效数字

[qq_36934345]

额，好像说错啦


应该是这样，那个xkh是增加的，对符合的计数，jyje（存在的字段）

[js14982]

无效数字是数据格式问题，不是语句问题

[js14982]

引用 5 楼 qq_36934345 的回复:

额，好像说错啦 应该是这样，那个xkh是增加的，对符合的计数，jyje（存在的字段）

你这一会行一会列的，你这个就不用转列，简单很多，等下

[js14982]

你的jysj是什么数据类型？

[qq_36934345]

引用 7 楼 js14982 的回复:

[quote=引用 5 楼 qq_36934345 的回复:] 额，好像说错啦 应该是这样，那个xkh是增加的，对符合的计数，jyje（存在的字段）

你这一会行一会列的，你这个就不用转列，简单很多，等下[/quote] 抱歉啦

[qq_36934345]

引用 8 楼 js14982 的回复:

你的jysj是什么数据类型？

数字字符型

[js14982]

select substr(jysj,1,10),jgh,count(skh) skh,count(jyje) jyje from cxjy_ls 
   where jgh in
   (select newjgh from xt_jghdzb 
         where   connect_by_isleaf=1 and newjgh in (select jgh from xt_gyb) 
          START WITH newjgh='53000000'  CONNECT BY PRIOR newjgh=sjjgh)
    and jysj>='2017-01-05 00:00:00' and jysj<='2017-02-10 23:59:59'
     and jydm in('000001','000001','000006','000002','000001') and jymc is not null  
     group by substr(jysj,1,10),jgh ;