SQL语句的问题

Question

错误信息是不是ORA-00979: not a GROUP BY expression
sch_shop2.shop_name没有出现在group by子句中，因此不能在select后
出现。

Answer 1

你的group by 中的字段数于select 后的不同 两者要保持一致。

Answer 2

select sch_tradehis.shop_id shop_id,sch_tradehis.term_id term_id,sch_tradehis.settle_date settle_date,
sch_shop2.shop_name shop_name,count(amount) count,nvl(sum(amount),0) sum from sch_shop2,sch_tradehis 
where 
sch_tradehis.shop_id=sch_shop2.shop_id and sch_tradehis.settle_date between '2002-06-02' and '2002-07-01' group by sch_tradehis.shop_id shop_id,sch_tradehis.term_id term_id,sch_tradehis.settle_date settle_date,sch_shop2.shop_name shop_name

Answer 3

select sch_tradehis.shop_id,
       sch_tradehis.term_id,
       sch_tradehis.settle_date,
       sch_shop2.shop_name,
       count(amount) count_amount,
       nvl(sum(amount),0) sum_amount
  from sch_shop2,sch_tradehis 
 where sch_tradehis.shop_id=sch_shop2.shop_id
   and sch_tradehis.settle_date between '2002-06-02' and '2002-07-01'
 group by sch_tradehis.shop_id,
          sch_tradehis.term_id,
          sch_tradehis.settle_date,
          sch_shop2.shop_name;