sql使用group by分组出现错误

Question

使用group by 分组查询时出现报错

SELECT
    '' aftersale_order_type,
    NULL aftersale_order_id,
    orig_head.order_type orig_order_type,
    ro.refund_order_no AS refundId,
    nvl( ro.refund_type, '-' ) AS refundType,
    ro.refund_status AS refundStatus,
    nvl( ro.express_number, '-' ) AS logisticsNumber,
    nvl( ro.apply_date, '0' ) AS refundDate,
    ro.return_quantity AS returnQuantity,
    nvl( ro.return_method, '0' ) AS returnWay,
    orig_head.order_id orderId,
    ro.refund_amount AS refundAmount,
    ( orig_head.TOTAL_AMOUNT + orig_head.FREIGHT_AMOUNT - orig_head.DISCOUNT_AMOUNT ) AS parentPrice,
CASE
        
        WHEN orig_head.sale_channel = 'ENTERPRISE_APPLET' THEN
        heu.WECHAT_USER_ID ELSE hu.MOBILE_NUMBER 
    END AS userId,
    nvl( orig_head.receiver_phone, '-' ) AS userTel,
    nvl( orig_head.receiver_mobile, '-' ) AS userPhone,
    ro.alipay_refund_lock AS alipayRefundLock,
    orig_head.ENCRYP_LEVEL,
CASE
        
        WHEN orig_head.sale_channel = 'ENTERPRISE_APPLET' THEN
        heu.ENCRYP_LEVEL ELSE hu.ENCRYP_LEVEL 
    END AS CUSER_ENCRYP,
    orig_head.sale_channel SALE_CHANNEL,
    ro.IS_REFUND_FAILED,
    rl.ACTUAL_REFUND_AMOUNT,
    sum( nvl( hot.cash_coupon_fee, 0 ) + nvl( hot.nocash_coupon_fee, 0 ) ) PAY_OFFER 
FROM
    HMCS_REFUND_ORDERS ro
    INNER JOIN hmod_order_lines orig_line ON orig_line.order_line_id = ro.order_line_id
    INNER JOIN hmod_order_headers orig_head ON orig_head.order_id = orig_line.order_id
    LEFT JOIN HMFD_EMPLOYEE_USERS heu ON heu.employee_user_id = orig_head.USER_ID
    LEFT JOIN HMFD_USERS hu ON hu.user_id = orig_head.USER_ID
    LEFT JOIN hmcs_refund_lines rl ON ro.REFUND_ORDER_NO = rl.refund_order_no 
    AND rl.payment_way = 'WECHATPAY'
    LEFT JOIN hmod_order_transactions hot ON ro.refund_order_no = hot.source_id 
    AND hot.CHANNEL_ID = 'WECHATPAY' 
WHERE
    orig_head.order_type IN ( 'DEFAULT', 'PRESALE', 'GIFTCARD' ) 
    AND ro.refund_order_no = '102133667' 
GROUP BY
ro.refund_order_no

报错信息
ORA-00979: not a GROUP BY expression

Answer 1

SELECT
    ro.refund_order_no AS refundId,
    sum(nvl(hot.cash_coupon_fee, 0 ) + nvl(hot.nocash_coupon_fee, 0 )) PAY_OFFER
FROM
    hmcs_refund_orders ro
    LEFT JOIN HMOD_ORDER_TRANSACTIONS hot ON ro.REFUND_ORDER_NO = hot.SOURCE_ID 
    AND hot.CHANNEL_ID = 'WECHATPAY'
WHERE
    ro.REFUND_ORDER_NO = '102133667' 
GROUP BY
    ro.REFUND_ORDER_NO

简化了一下发现正常了