- Notifications
You must be signed in to change notification settings - Fork 59
More SQL Function, more Group by
Bee edited this page Feb 4, 2021 · 1 revision
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; ...... }