(十六)More SQL Function,select subject,max(score) as maxScore,avg(score)

More SQL Function, more Group by

How to use ORM Bee develop when the sql like: select examno, subject,max(score) ,avg(score) ,min(score) from scores

How to use ORM Bee develop the functoin as below ?

select classno, term, examno, subject,max(score) as maxScore,avg(score) as avgScore,min(score) as minScore 
from scores where status!='FIN' group by term,examno,subjectno,subject order by classno,term,examno,subjectno

201     1     2020年秋八年级期末     语文     100     73.23     52     
201     1     2020年秋八年级期末     数学     100     71.25     49     
201     1     2020年秋八年级期末     英语     100     70.76     53

If you want to more function like : max(score) as maxScore,avg(score) as avgScore,min(score) as minScore,
You can develop with ORM Bee like below:

		Condition condition=new ConditionImpl();
		
		condition
		.selectField("classno,term,examno,subject")
		.selectFun(FunctionType.MAX, "score","maxScore")
		.selectFun(FunctionType.AVG, "score","avgScore")
		.selectFun(FunctionType.MIN, "score","minScore");
		
		condition.op("status", Op.nq, "FIN"); 
		
		condition
		.groupBy("term,examno,subjectno,subject")
		.orderBy("classno,term,examno,subjectno")
		;
		
		Scores scores=new Scores();
		String r=suidRich.selectJson(scores, condition); // json result
		System.out.println(r);
		
		List<String[]> listString=suidRich.selectString(scores, condition);  // string array result
		String str[];
		for (int i = 0; i < listString.size(); i++) {
			str=listString.get(i);
			for (int j = 0; j < str.length; j++) {
				System.out.print(str[j]+"     ");
			}
			System.out.println();
		}

You can get the result type as below :
json result
string array result
define a new response entity

List<ScoresResponse> list=suidRich.select(new ScoresResponse(), condition);


// ScoresResponse class :

@Entity("Scores")
public class ScoresResponse {
	
    //the field name same as Scores
    private String classno;
    private String term;
    private String examno;
    private String subject;
    private Double score;
    
    //the field name define in Condition
    private Double maxScore;
    private Double avgScore;
    private Double minScore;

    ......
}