-----------------------------------------
avg | amount | total | type | date
-----------------------------------------
0 11 12 1 2008-11-30
-----------------------------------------
20 21 22 2 2008-11-29
------------------------------------------
30 31 32 3 2008-11-28
-----------------------------------------
0 11 12 1 2008-11-28
-----------------------------------------
20 21 22 2 2008-11-30
------------------------------------------
30 31 32 3 2008-11-29
......
求一SQL:
统计出在2008-11-28至2008-11-30期间,
当type等于1时,amount,total的总和
当type等于2时,avg,amount的总和
当type等于3时,avg,amount,total的总和
avg | amount | total | type | date
-----------------------------------------
0 11 12 1 2008-11-30
-----------------------------------------
20 21 22 2 2008-11-29
------------------------------------------
30 31 32 3 2008-11-28
-----------------------------------------
0 11 12 1 2008-11-28
-----------------------------------------
20 21 22 2 2008-11-30
------------------------------------------
30 31 32 3 2008-11-29
......
求一SQL:
统计出在2008-11-28至2008-11-30期间,
当type等于1时,amount,total的总和
当type等于2时,avg,amount的总和
当type等于3时,avg,amount,total的总和
解决方案 »
- 怎么PLSQL编译过程总不行,一会还报锁住
- ORACLE 无法从套接字读取更多的数据 错误
- 跪求各位高手 ORancle 版本兼容问题
- rownum执行速度超慢
- TO_CHAR(数值型数据)的问题,谢谢!
- 一个简单的插入数据问题---急
- 如何查出时间字段中,小时、分、秒都为0的记录?
- 怪异!急!急!急!关于SQL*Plus工作单与Oracle Enterprise Manager Console之间的一个怪异的问题?
- 在oracle8i中有没有类似于8.0.5的Storage Manager的工具?
- 关于ORACLE执行一个过程的问题,在线等待!分不够你说,要多少只要我有都给你!!
- 日期转换
- oracle rman备份!!!!
--试一下,看是不是这个意思:select type, sum(amount), sum(total)
from tableA
where type = 1
and to_char(date, 'yyyy-mm-dd') >= '2008-11-28'
and to_char(date, 'yyyy-mm-dd') <= '2008-11-30'
group by type;select type, sum(avg), sum(amount)
from tableA
where type = 2
and to_char(date, 'yyyy-mm-dd') >= '2008-11-28'
and to_char(date, 'yyyy-mm-dd') <= '2008-11-30'
group by type;select type, sum(avg), sum(amount), sum(total)
from tableA
where type = 3
and to_char(date, 'yyyy-mm-dd') >= '2008-11-28'
and to_char(date, 'yyyy-mm-dd') <= '2008-11-30'
group by type;
(
avg varchar2(10),
amount number(10),
total number(10),
type varchar2(10),
dates date
)select decode(type,
'1',
sum(amount + total) over(partition by type),
'2',
sum(avg + amount) over(partition by type),
'3',
sum(avg + amount + total) over(partition by type),
0)
from a00
where to_char(dates, 'yyyy-mm-dd') between '2008-11-28' and '2008-11-30'
,sum(case when type=1 then amount + total else 0 end) a1
,sum(case when type=2 then avg+amount else 0 end ) a2
,sum(case when type=3 then avg + amount + total else 0 end) a3
from t
where date between to_date('2008-11-28', 'yyyy-mm-dd') and to_date('2008-11-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
,sum(case when type=2 then avg+amount else 0 end ) a2
,sum(case when type=3 then avg + amount + total else 0 end) a3
from t
where date between to_date('2008-11-28', 'yyyy-mm-dd') and to_date('2008-11-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
我要的是一条SQl语句
其实结果是这样的:
amountFortype1=22,totalFortype1=24,avgFortype2=40,amountFortype2=32,avgFortype3=50,amountFortype3=52,totalFortype3=64
我要的是一条SQl语句
其实结果是这样的:
amountFortype1=22,totalFortype1=24,
avgFortype2=40,amountFortype2=32,
avgFortype3=50,amountFortype3=52,totalFortype3=64
SQL> SELECT * FROM TABLE_NAME; AVGS AMOUNT TOTAL TYPES DATES
---------- ---------- ---------- ---------- -----------
0 11 12 1 2008-11-30
20 21 22 2 2008-11-29
30 31 32 3 2008-11-28
0 11 12 1 2008-11-28
20 21 22 2 2008-11-30
30 31 32 3 2008-11-296 rows selectedSQL> SELECT TYPES,
2 SUM(DECODE(TYPES,1,NULL,2,AVGS,3,AVGS,NULL)) AVGS,
3 SUM(DECODE(TYPES,1,AMOUNT,2,AMOUNT,3,AMOUNT,NULL)) AMOUNTS,
4 SUM(DECODE(TYPES,1,TOTAL,2,NULL,3,TOTAL,NULL)) TOTAL
5 FROM TABLE_NAME TT
6 WHERE DATES >= TO_DATE('2008-11-28','YYYY-MM-DD')
7 AND DATES <= TO_DATE('2008-11-30','YYYY-MM-DD')
8 GROUP BY TYPES; TYPES AVGS AMOUNTS TOTAL
---------- ---------- ---------- ----------
1 22 24
2 40 42
3 60 62 64SQL> SELECT MAX(DECODE(TYPES, 1, SUM_AMOUNT, NULL)) AMOUNTFORTYPE1,
2 MAX(DECODE(TYPES, 1, SUM_TOTAL, NULL)) TOTALFORTYPE1,
3 MAX(DECODE(TYPES, 2, SUM_AVG, NULL)) AVGFORTYPE2,
4 MAX(DECODE(TYPES, 2, SUM_AMOUNT, NULL)) AMOUNTFORTYPE2,
5 MAX(DECODE(TYPES, 3, SUM_AVG, NULL)) AVGFORTYPE3,
6 MAX(DECODE(TYPES, 3, SUM_AMOUNT, NULL)) AMOUNTFORTYPE3,
7 MAX(DECODE(TYPES, 3, SUM_TOTAL, NULL)) TOTALFORTYPE3
8 FROM (SELECT TYPES,
9 SUM(AVGS) SUM_AVG,
10 SUM(AMOUNT) SUM_AMOUNT,
11 SUM(TOTAL) SUM_TOTAL
12 FROM TABLE_NAME TT
13 WHERE DATES >= TO_DATE('2008-11-28', 'YYYY-MM-DD')
14 AND DATES <= TO_DATE('2008-11-30', 'YYYY-MM-DD')
15 GROUP BY TYPES) ZZ;AMOUNTFORTYPE1 TOTALFORTYPE1 AVGFORTYPE2 AMOUNTFORTYPE2 AVGFORTYPE3 AMOUNTFORTYPE3 TOTALFORTYPE3
-------------- ------------- ----------- -------------- ----------- -------------- -------------
22 24 40 42 60 62 64
SQL> SELECT 'AMOUNTFORTYPE1='||MAX(DECODE(TYPES, 1, SUM_AMOUNT, NULL))||
2 ', TOTALFORTYPE1='||MAX(DECODE(TYPES, 1, SUM_TOTAL, NULL))||
3 ', AVGFORTYPE2='||MAX(DECODE(TYPES, 2, SUM_AVG, NULL))||
4 ', AMOUNTFORTYPE2='||MAX(DECODE(TYPES, 2, SUM_AMOUNT, NULL))||
5 ', AVGFORTYPE3='||MAX(DECODE(TYPES, 3, SUM_AVG, NULL))||
6 ', AMOUNTFORTYPE3='||MAX(DECODE(TYPES, 3, SUM_AMOUNT, NULL))||
7 ', TOTALFORTYPE3='||MAX(DECODE(TYPES, 3, SUM_TOTAL, NULL)) MYSTR
8 FROM (SELECT TYPES,
9 SUM(AVGS) SUM_AVG,
10 SUM(AMOUNT) SUM_AMOUNT,
11 SUM(TOTAL) SUM_TOTAL
12 FROM TABLE_NAME TT
13 WHERE DATES >= TO_DATE('2008-11-28', 'YYYY-MM-DD')
14 AND DATES <= TO_DATE('2008-11-30', 'YYYY-MM-DD')
15 GROUP BY TYPES) ZZ;MYSTR
---------------------------------------------------------------------------------------------------------------------------
AMOUNTFORTYPE1=22, TOTALFORTYPE1=24, AVGFORTYPE2=40, AMOUNTFORTYPE2=42, AVGFORTYPE3=60, AMOUNTFORTYPE3=62, TOTALFORTYPE3=64
谢谢mantisXF
结贴!