没有日期字段,怎么按的月呢?
不好意思,表太多了没都贴上,
组员工资表
id
personname
month;
salary
我主要想知道这个统计功能如何实现,特别是如何把组名插入到该组对应的组员下面呢?非常感谢!
换个思路,不要想着把组名插入组员下面,而是把组员插到组名上面
sql server 2005以上可以试试pivot函数,如果是oracle就不清楚了!
用Partition by 累进分界线求和
select * from (
select EID , ENAME, GROUPID, SALARY from TEST.TE
union all
select 10000 as eid,gname as ename ,gid as groupid,sum(salary)as salary from test.te as te ,test.tg as tg where te.groupid=tg.gid group by gid ,gname
)t order by groupid,eid
eid ename groupid salary
1'张三'1100
2'李四'1200
3'王五'1300
10000'A'1600
4'刘牛'2400
5'杨七'2500
6'朱八'2600
10000'B'21500
select * from (
select EID , ENAME, GROUPID, SALARY from TEST.TE
union all
select 10000 as eid,gname as ename ,gid as groupid,sum(salary)as salary from test.te as te ,test.tg as tg where te.groupid=tg.gid group by gid ,gname
union all
select 1000000 as eid,'全部' as ename ,10000 as groupid,sum(salary)as salary from test.te as te ,test.tg as tg where te.groupid=tg.gid
)t order by groupid,eid
eid ename groupid salary
1'张三'1100
2'李四'1200
3'王五'1300
10000'A'1600
4'刘牛'2400
5'杨七'2500
6'朱八'2600
10000'B'21500
1000000'全部'100002100
楼上高手,谢谢了发分了
select * from (
select EID , ENAME, GROUPID, SALARY from TEST.TE
union all
select 10000 as eid,gname as ename ,gid as groupid,sum(salary)as salary from test.te as te ,test.tg as tg where te.groupid=tg.gid group by gid ,gname
union all
select 1000000 as eid,'全部' as ename ,10000 as groupid,sum(salary)as salary from test.te as te ,test.tg as tg where te.groupid=tg.gid
)t order by groupid,eid
eid ename groupid salary
另外还想问下,如何把你写的SQL直接在HIBERNATE中执行啊,是用createSQLQuery么?但这么复杂的SQL怎么直接调用啊,能再指点下么,谢谢了