不好意思,表太多了没都贴上,组员工资表 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,eideid ename groupid salary 1 '张三' 1 100 2 '李四' 1 200 3 '王五' 1 300 10000 'A' 1 600 4 '刘牛' 2 400 5 '杨七' 2 500 6 '朱八' 2 600 10000 'B' 2 1500
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 '张三' 1 100 2 '李四' 1 200 3 '王五' 1 300 10000 'A' 1 600 4 '刘牛' 2 400 5 '杨七' 2 500 6 '朱八' 2 600 10000 'B' 2 1500 1000000 '全部' 10000 2100
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怎么直接调用啊,能再指点下么,谢谢了
id
personname
month;
salary
我主要想知道这个统计功能如何实现,特别是如何把组名插入到该组对应的组员下面呢?非常感谢!
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,eideid ename groupid salary
1 '张三' 1 100
2 '李四' 1 200
3 '王五' 1 300
10000 'A' 1 600
4 '刘牛' 2 400
5 '杨七' 2 500
6 '朱八' 2 600
10000 'B' 2 1500
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 '张三' 1 100
2 '李四' 1 200
3 '王五' 1 300
10000 'A' 1 600
4 '刘牛' 2 400
5 '杨七' 2 500
6 '朱八' 2 600
10000 'B' 2 1500
1000000 '全部' 10000 2100
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怎么直接调用啊,能再指点下么,谢谢了