使用sql实现统计查询,
使用背景:对药品的财务情况做出统计,下面是一个测试表建表语句
CREATE TABLE "TEST"
( "ID" NUMBER, //药品id,唯一标示某一种药品
"DANJIA" NUMBER, //药品单价
"GESHU" NUMBER //药品交易数量
)
测试数据如下:
sql语句如下:
select '标记', id,danjia,geshu from jilinadm.test t
union select '小结', id,0,s from (select id,sum(geshu*danjia) s from jilinadm.test group by id)
union select '总结', 100,0,s from (select sum(geshu*danjia) s from jilinadm.test)order by id
其中jilinadm是我的用户名
结果如下:
使用背景:对药品的财务情况做出统计,下面是一个测试表建表语句
CREATE TABLE "TEST"
( "ID" NUMBER, //药品id,唯一标示某一种药品
"DANJIA" NUMBER, //药品单价
"GESHU" NUMBER //药品交易数量
)
测试数据如下:
ID | DANJIA | GESHU | |
2 | 2 | 3 | |
1 | 23 | 2 | |
1 | 23 | 1 | |
1 | 23 | 6 | |
sql语句如下:
select '标记', id,danjia,geshu from jilinadm.test t
union select '小结', id,0,s from (select id,sum(geshu*danjia) s from jilinadm.test group by id)
union select '总结', 100,0,s from (select sum(geshu*danjia) s from jilinadm.test)order by id
其中jilinadm是我的用户名
结果如下:
'标记' | ID | DANJIA | GESHU |
标记 | 1 | 23 | 1 |
标记 | 1 | 23 | 2 |
标记 | 1 | 23 | 6 |
小结 | 1 | 0 | 207 |
标记 | 2 | 2 | 3 |
小结 | 2 | 0 | 6 |
总结 | 100 | 0 | 213 |
转载于:https://blog.51cto.com/lee2cto/152829