Find out the various types of amounts in the database, summary (the Art of SQL statement writing)

This example is used in small projects, want to share to everyone, although simple, but may be useful to everyone.

Example: Bookkeeping system

One:

Database: Water records, recorded a lot of consumption types. Such as: The type can be known through the typeID field. (There is a table of consumption types, of course)

Two: SQL statement query

Description: Mainly the use of case statements, according to different circumstances, the sum of different values to get the results we want!

1      Public List getsqlquerycolumnlist (String sql) 2     {3         list l=getsession (). createsqlquery (SQL). List (); 4         return l; 5 6     }

Basedao is my various basic database operation functions, we can build it, in fact, mainly with the above function.

If you use Hibernate, you can get sessionfactory sessionfactory.

Sessionfactory.getcurrentsession () Gets the session, then operation, that is, the above function.

1  //find out all kinds of money, money, money, money, money, money! 2String moneysql= "Select sum (case is typeid=1 then money else 0 end) as Cateen," +3"Sum (case if typeid=2 then money else 0 end) as car," +4"Sum (case if typeid=3 then money else 0 end) as entertainment," +5"Sum (case when typeid=4 then money else 0 end) as office," +6"Sum (case if typeid=5 then money else 0 end) as accommodation," +7"Sum (case if typeid=6 then money else 0 end) as traffic," +8"Sum (case when typeid=7 then money else 0 end) as service," +9"Sum (case if Typeid=8 then money else 0 end) as gift," +Ten"Sum (case if typeid=9 then money else 0 end) as training," + One"Sum (case if typeid=10 then money else 0 end) as Othermoney," + A"Sum (money) as Totalmoney" + -"From Db_workinfo.tb_daily_account where rigisteruser= ' wuhuanbin ' and ID between 2 and 30"; -  theList listformoney=basedao.getsqlquerycolumnlist (moneysql); -  -Iterator moneyiterator=listformoney.iterator (); -Object[] O=(object[]) moneyiterator.next (); +System.out.println ("Dining" +o[0]); -SYSTEM.OUT.PRINTLN ("Vehicle use" +o[1]); +SYSTEM.OUT.PRINTLN ("Project Entertainment" +o[2]); ASYSTEM.OUT.PRINTLN ("office equipment" +o[3]); atSystem.out.println ("Accommodation fe fee" +o[4]); -SYSTEM.OUT.PRINTLN ("Transportation" +o[5]); -SYSTEM.OUT.PRINTLN ("Service Charge" +o[6]); -SYSTEM.OUT.PRINTLN ("Gift fee" +o[7]); -SYSTEM.OUT.PRINTLN ("Training fee" +o[8]); -System.out.println ("Other fees" +o[9]);  in  -System.out.println ("Total de cost:" +o[10]);

Check is a water table, according to the type of consumption ID Oh!

Find out the various types of amounts in the database, summary (the Art of SQL statement writing)