关于oracle的问题。本人菜鸟，请高手指点下。

Question

vv_sqlcmd := 'insert into tsd_subs_points_'|| vv_month ||'(      '||
                 '            SUBS_PTS_ID,                '||--1
                 '            CURRENT_PTS,                '||--2
                 '            ACC_PTS,                    '||--3
                 '            SUBS_INSTANCE_ID,           '||--4
                 '            STATUS,                     '||--5
                 '            CONSUME_PTS,                '||--6
                 '            ONNET_PTS,                  '||--7
                 '            AWARD_PTS,                  '||--8
                 '            CREDIT_PTS)                 '||--9
                 ' select    b.SUBS_PTS_ID,               '||--1 
                 '           b.CURRENT_PTS,               '||--2 
                 '           a.ACC_PTS,                   '||--3 
                 '           a.SUBS_ID,                   '||--4 
                 '           a.STATUS,                    '||--5 
                 ' sum(decode(b.pts_typ_id,''消费'',''消费'',0)), '||--6
                 ' sum(decode(b.pts_typ_id,''在网'',''在网'',0)), '||--7 
                 ' sum(decode(b.pts_typ_id,''奖励'',''奖励'',0)), '||--8
                 ' sum(decode(b.pts_typ_id,''信用'',''信用'',0))  '||--9 
                 ' from      subs_points a,               '||
                 '           subs_typ_points b            '||
                 ' group by  b.pts_typ_id                 '||
                 ' where     a.subs_pts_id=b.subs_pts_id  ';
    execute immediate vv_sqlcmd;
    commit;
本人初学者，想问问高手们这个应该怎么写，我知道group by后面应该接having，但是我需要后面的条件，应该怎么改。后者有没有别的写法。很想给点分，但是我没有，谢谢好心人。

Answer 1

' group by  b.pts_typ_id                '|| 
' where   a.subs_pts_id=b.subs_pts_id    ';改成
where a.subs_pts_id=b.subs_pts_id  
group by  b.pts_typ_id

Answer 2

改过之后报这个-979  ORA-00979: not a GROUP BY expression错
能帮我再改改吗

Answer 3

   
没有时用聚集函数的列 要放在group by 后面，
下面不知道是不是你要的结果select    b.SUBS_PTS_ID,               
           b.CURRENT_PTS, 
           a.ACC_PTS,  
           a.SUBS_ID,                  
           a.STATUS,                   
           sum(decode(b.pts_typ_id,''消费'',''消费'',0)), 
           sum(decode(b.pts_typ_id,''在网'',''在网'',0)), 
           sum(decode(b.pts_typ_id,''奖励'',''奖励'',0)), 
           sum(decode(b.pts_typ_id,''信用'',''信用'',0))  
            from      subs_points a,              | 
                     subs_typ_points b
            where    a.subs_pts_id=b.subs_pts_id             
            group by    b.SUBS_PTS_ID,               
                        b.CURRENT_PTS, 
                        a.ACC_PTS,  
                        a.SUBS_ID,                  
                        a.STATUS   

Answer 4

太感谢了，搞定了，就是不太明白为什么group by 加那几个字段而不是b.pts_typ_id
总之，多谢！！

Answer 5

 你还是查下资料看下group by 的含义和用法吧 

Answer 6

            group by    b.SUBS_PTS_ID,               
                        b.CURRENT_PTS, 
                        a.ACC_PTS,  
                        a.SUBS_ID,                  
                        a.STATUS  

Answer 7

如果用 GROUP BY 函数，那SELECT 后除了可直接引用所GROUP BY 的字段外，其他SELECT 字段都要进行GROUP判断或计算(MAX、SUM、MIN、AVG、....)。你这题，如果保证两张表关联的字段是唯一的，那么可以再不是GROUP BY的字段，加假判断MAX()
' select    b.SUBS_PTS_ID,              '||--1 
                '          MAX(b.CURRENT_PTS),              '||--2 
                '          MAX(a.ACC_PTS),                  '||--3 
                '          MAX(a.SUBS_ID),                  '||--4 
                '          MAX(a.STATUS),                    '||--5 
                ' sum(decode(b.pts_typ_id,''消费'',''消费'',0)), '||--6 
                ' sum(decode(b.pts_typ_id,''在网'',''在网'',0)), '||--7 
                ' sum(decode(b.pts_typ_id,''奖励'',''奖励'',0)), '||--8 
                ' sum(decode(b.pts_typ_id,''信用'',''信用'',0))  '||--9 
                ' from      subs_points a,              '|| 
                '          subs_typ_points b            '|| 
                ' where    a.subs_pts_id=b.subs_pts_id  '||                
                ' group by  b.pts_typ_id                ';  from 

Answer 8

晕，显示有点问题。' select    b.SUBS_PTS_ID,              '||--1 
                '          MAX(b.CURRENT_PTS),              '||--2 
                '          MAX(a.ACC_PTS),                  '||--3 
                '          MAX(a.SUBS_ID),                  '||--4 
                '          MAX(a.STATUS),                    '||--5 
                ' sum(decode(b.pts_typ_id,''消费'',''消费'',0)), '||--6 
                ' sum(decode(b.pts_typ_id,''在网'',''在网'',0)), '||--7 
                ' sum(decode(b.pts_typ_id,''奖励'',''奖励'',0)), '||--8 
                ' sum(decode(b.pts_typ_id,''信用'',''信用'',0))  '||--9 
                ' from      subs_points a,              '|| 
                '          subs_typ_points b            '|| 
                ' where    a.subs_pts_id=b.subs_pts_id  '||               
                ' group by  b.pts_typ_id                ';  from