如何在mysql表中选择、平均和排序

i have a table in mySql like in this picture

我想编写一个查询，其结果将按 LESSON 列分组，并添加新行，该新行是 LESSON 列的平均值和 CNT 列值之和......
对于这个查询我使用这个

我使用这个查询，但它给出的结果如图 3 所示，在这种情况下我无法按 PERCENT 排序

select no, STUD_ID,CLASS,LESSON, AVG(PERC) as PERC,SUM(CNT) as CNT from t_lesson where LESSON='CHEM' group by CLASS
union all
select no,STUD_ID,CLASS,'AVERAGE' as LESSON, AVG(PERC) as PERC, SUM(CNT) as CNT from t_lesson where LESSON='CHEM' group by LESSON

看起来您正在选择所有行lesson is CHEM，然后您需要一个额外的行来表示百分比的平均值。怎么样：

select *
from (
  -- this part gets all the "CHEM" rows
  select * 
  from <your_table_name>
  where lesson = "CHEM"
  union
  -- this parts selects the aggregate row
  select 
    NULL            as `no`, 
    NULL            as `stud_id`, 
    NULL            as `class`,
    "average"       as `lesson`,
    avg(percentage) as `perc`, 
    sum(count)      as `cnt`
  from <your_table_name>
  where lesson = "CHEM"
) q
order by `perc` desc;

请注意，排序是由外部查询执行的。