sql语句问题

Question

SELECT t2.gelationtime    ,t2.PAP_ENERGY   ,t2.PRP_ENERGY   ,t2.RAP_ENERGY   ,t2.RRP_ENERGY
                    ,MAX(t3.p) as maxP   ,min(t3.p) as minP  ,t4.pf  ,t1.*
FROM v_relation t1
LEFT JOIN t_curve_en t2 ON t1.mped_id = t2.mped_id
LEFT JOIN T_curve_p t3 ON t1.mped_id = t3.mped_id
AND t2.gelationtime = t3.gelationtime
LEFT JOIN T_curve_pf t4 ON t1.mped_id = t4.mped_id
AND t3.gelationtime = t4.gelationtime
WHERE (1=1)  AND t2.gelationtime=:t2_gelationtime 
GROUP BY t2.gelationtime    ,t2.PAP_ENERGY  ,t2.PRP_ENERGY   ,t2.RAP_ENERGY
                    ,t2.RRP_ENERGY   ,t3.p   ,t4.pf ,t1.meter_id ,t1.meter_name   ,t1.pt    ,t1.ct
                    ,t1.U_SPEC  ,t1.doublerate ,t1.deviceno  ,t1.mped_id  ,t1.mped_name  ,t1.terminal_id
                    ,t1.mp_mode   ,t1.terminal_name  ,t1.company_id ,t1.line_id    ,t1.substation_id
                    ,t1.cust_qua   ,t1.terminal_number  ,t1.cust_id  ,t1.cust_name  ,t1.trade_type报错了：“不是Group By表达式”
请问哪里出错了？

Answer 1

------这样肯定没问题，分组的列必须在选择列中出现
SELECT t2.gelationtime,
       t2.PAP_ENERGY,
       t2.PRP_ENERGY,
       t2.RAP_ENERGY,
       t2.RRP_ENERGY,
       t3.p,
       t4.pf,
       t1.meter_id,
       t1.meter_name,
       t1.pt,
       t1.ct,
       t1.U_SPEC,
       t1.doublerate,
       t1.deviceno,
       t1.mped_id,
       t1.mped_name,
       t1.terminal_id,
       t1.mp_mode,
       t1.terminal_name,
       t1.company_id,
       t1.line_id,
       t1.substation_id,
       t1.cust_qua,
       t1.terminal_number,
       t1.cust_id,
       t1.cust_name,
       t1.trade_type,
       MAX(t3.p) as maxP,
       min(t3.p) as minP,
  FROM v_relation t1
  LEFT JOIN t_curve_en t2 ON t1.mped_id = t2.mped_id
  LEFT JOIN T_curve_p t3 ON t1.mped_id = t3.mped_id
                        AND t2.gelationtime = t3.gelationtime
  LEFT JOIN T_curve_pf t4 ON t1.mped_id = t4.mped_id
                         AND t3.gelationtime = t4.gelationtime
 WHERE (1 = 1)
   AND t2.gelationtime = :t2_gelationtime
 GROUP BY t2.gelationtime,
          t2.PAP_ENERGY,
          t2.PRP_ENERGY,
          t2.RAP_ENERGY,
          t2.RRP_ENERGY,
          t3.p,
          t4.pf,
          t1.meter_id,
          t1.meter_name,
          t1.pt,
          t1.ct,
          t1.U_SPEC,
          t1.doublerate,
          t1.deviceno,
          t1.mped_id,
          t1.mped_name,
          t1.terminal_id,
          t1.mp_mode,
          t1.terminal_name,
          t1.company_id,
          t1.line_id,
          t1.substation_id,
          t1.cust_qua,
          t1.terminal_number,
          t1.cust_id,
          t1.cust_name,
          t1.trade_type

Answer 2

楼主select语句中 t1.*中并不是所有栏位都是你表v_relation t1的group by 栏位
如果是分组group by，那么select 语句中的栏位必须是group by 的栏位和聚合函数。

Answer 3

没有使用聚合函数的字段都要 group by.

Answer 4

SELECT t2.gelationtime ,t2.PAP_ENERGY ,t2.PRP_ENERGY ,t2.RAP_ENERGY ,t2.RRP_ENERGY
  ,MAX(t3.p) as maxP ,min(t3.p) as minP ,t4.pf ,t1.meter_id ,t1.meter_name ,t1.pt ,t1.ct
  ,t1.U_SPEC ,t1.doublerate ,t1.deviceno ,t1.mped_id ,t1.mped_name ,t1.terminal_id
  ,t1.mp_mode ,t1.terminal_name ,t1.company_id ,t1.line_id ,t1.substation_id
  ,t1.cust_qua ,t1.terminal_number ,t1.cust_id ,t1.cust_name ,t1.trade_typeFROM v_relation t1
LEFT JOIN t_curve_en t2 ON t1.mped_id = t2.mped_id
LEFT JOIN T_curve_p t3 ON t1.mped_id = t3.mped_id
AND t2.gelationtime = t3.gelationtime
LEFT JOIN T_curve_pf t4 ON t1.mped_id = t4.mped_id
AND t3.gelationtime = t4.gelationtime
WHERE (1=1) AND t2.gelationtime=:t2_gelationtime  
GROUP BY t2.gelationtime ,t2.PAP_ENERGY ,t2.PRP_ENERGY ,t2.RAP_ENERGY
  ,t2.RRP_ENERGY ,t3.p ,t4.pf ,t1.meter_id ,t1.meter_name ,t1.pt ,t1.ct
  ,t1.U_SPEC ,t1.doublerate ,t1.deviceno ,t1.mped_id ,t1.mped_name ,t1.terminal_id
  ,t1.mp_mode ,t1.terminal_name ,t1.company_id ,t1.line_id ,t1.substation_id
  ,t1.cust_qua ,t1.terminal_number ,t1.cust_id ,t1.cust_name ,t1.trade_type