查出数据库各种类型的金额,汇总(SQL语句编写的艺术)

 

本实例是在做小项目的时候用到的,想分享给大家,虽然简单,但可能对大家有用。!!!

 

实例:记账系统

 

一:

数据库: 流水记录,记录的消费类型有很多。如下图:通过typeId字段可以知道类型。(当然这里还有一张消费类型表哦)

查出数据库各种类型的金额,汇总(SQL语句编写的艺术)

 

二:sql语句查询

 

说明:主要是case语句的运用,根据不同情况,对不同的值进行sum运算得到我们想要的结果!

 

1     public List getSQLqueryColumnList(String sql)
2     {
3         List l=getSession().createSQLQuery(sql).list();
4         return l;
5 
6     }

 

baseDao是我各种基础数据库操作的函数,大家可以自行构建,其实主要是用上面的函数.

 

如果用的是hibernate  可以拿到sessionFactory  sessionFactory.

 

SessionFactory.getCurrentSession() 得到session,再操作,即上述函数。

 

 1  //查出各种钱钱钱钱钱钱钱的汇总!
 2           String Moneysql="select  sum(case when typeid=1  then  money else 0 end ) as cateen,"+
 3                 "sum(case when typeid=2  then  money else 0 end ) as car,"+
 4                 "sum(case when 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 when typeid=5  then  money else 0 end ) as accommodation,"+
 7                 "sum(case when 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 when typeid=8  then  money else 0 end ) as gift,"+
10                 "sum(case when typeid=9  then  money else 0 end ) as training,"+
11                 "sum(case when typeid=10  then  money else 0 end ) as othermoney,"+
12                 "sum(money) as totalmoney "+
13                 "from db_workinfo.tb_daily_account where rigisteruser=‘wuhuanbin‘ and id between 2 and 30";
14 
15         List listForMoney=baseDao.getSQLqueryColumnList(Moneysql);
16 
17         Iterator moneyIterator=listForMoney.iterator();
18         Object[] o=(Object[])moneyIterator.next();
19         System.out.println("餐饮"+o[0]);
20         System.out.println("车辆使用 "+o[1]);
21         System.out.println("项目娱乐 "+o[2]);
22         System.out.println("办公设备 "+o[3]);
23         System.out.println("住宿fe费 "+o[4]);
24         System.out.println("交通费 "+o[5]);
25         System.out.println("服务费 "+o[6]);
26         System.out.println("礼品费 "+o[7]);
27         System.out.println("培训费 "+o[8]);
28         System.out.println("其他费 "+o[9]);      
29 
30         System.out.println("总de费用: "+o[10]);


//查的是流水表,根据是消费类型ID哦!

查出数据库各种类型的金额,汇总(SQL语句编写的艺术)

上一篇:MVC3+jquery Uploadify 上传文件


下一篇:SqlServer 树结构数据 子节点父节点的查询