SELECT A.ID,A.MONTH,B.POINTS
FROM
(SELECT ID,COL1 AS MONTH
FROM YOURTABLENAME
WHERE COL1='MONTH'
) A
,(SELECT ID,COL1 AS POINTS
FROM YOURTABLENAME
WHERE COL1='POINTS'
) B
WHERE
A.ID=B.ID
FROM
(SELECT ID,COL1 AS MONTH
FROM YOURTABLENAME
WHERE COL1='MONTH'
) A
,(SELECT ID,COL1 AS POINTS
FROM YOURTABLENAME
WHERE COL1='POINTS'
) B
WHERE
A.ID=B.ID
解决方案 »
- 关于oracle11g R2 OEM
- oracle cursor select时没有数据弹出循环
- oracle9i不能隐式把数字型转换成字符型了,如字符型字段=数字型
- oracle锁表后,系统能否自动解锁?(急)
- UTL_SMTP发邮件问题
- 最简单的存储过程,为什么出错?
- 怎样用语句截去每级单位(传入的单位编码是不知道的)后面的0 ?
- 如何捕捉这类异常:删除一条数据,而这条数据被其它数据所依赖,无法删除?
- 用vb外接程序可视化数据管理器连接oracle报错,为何?
- 请教大牛一个oracle语句问题,
- 请问:如何实现对两个数据库的操作阿?
- 大侠快来看看啊,调试这oracle 9i的jsp论坛时怎么总报这样的错误啊?(解决问题后送100分)
insert into t values('1','month',10);
insert into t values('1','points',1000);
insert into t values('2','month',12);
insert into t values('2','points',5000);select id,sum(decode(a,'month',b,0))month,
sum(decode(a,'points',b,0))points
from t
group by id;
SELECT ID,COL2 AS MONTH
FROM YOURTABLENAME
WHERE COL1='MONTH'
而不是
SELECT ID,COL1 AS MONTH
FROM YOURTABLENAME
WHERE COL1='MONTH'而且在关联时如果不能保证每一个号码都有Month 和Points最好写成外关联.
总和而言就是
SELECT A.ID,A.MONTH,B.POINTS
FROM
(SELECT ID,COL2 AS MONTH
FROM YOURTABLENAME
WHERE COL1='MONTH'
) A
,(SELECT ID,COL2 AS POINTS
FROM YOURTABLENAME
WHERE COL1='POINTS'
) B
WHERE
A.ID(+)=B.ID(+)
---------- -------------------- ----------
1 month 10
1 points 1000
2 month 12
2 points 5000SQL> select id,sum(decode(month,'month',p)) month,sum(decode(month,'points',p)) points from t_1 group by id
2 ; ID MONTH POINTS
---------- ---------- ----------
1 10 1000
2 12 5000SQL>