现有一批数据,每个CODE对应有10条数据,现在要把他合并成一条数据,就是把每条数据的其中一个字段合并到一条记录上去,新的这条记录的字段会增加9个.除了用子查询之外还有别的办法吗?
解决方案 »
- 统计分析后查询变慢
- oracle查询动态列
- 请教oralce 10G oem打不开问题
- 谁能让找个Oracle数据库服务器彻底瘫痪?
- 欲求oracle.com.cn的邀请码
- 求一sql语句!~~!~
- 简单 oracle问题(50分)
- Oracle 插入日期时,提示'文字与格式字符串不匹配'
- 请教一个random的问题,谢谢。
- 我想给一用户test授于DBA角色,但 在sqlplus下,grant dba to test;出错:不存在作用“dba”???
- 请教一个imp问题 表内有大量XML信息 报错是申请不到内存
- MATERIALIZED VIEW ENABLE QUERY REWRITE的问题,困扰了N天了
1 1 1
1 1 2
.
.
.
1 1 10
合并后数据变成
id1 id2 shuliang1 shuliang2 shuliang3 ... shuliang10
1 1 1 2 3 10
1 1 1
1 1 2
.
select T1.id1, T1.id2, T1.shuliang shuliang1,T2.shuliang shuliang2,...T10.shuliang
from Table T1,
Table T2,
Table T3,
...
Table T9,
Table T10
where
T1.id1=T2.id1(+)
and T1.id2=T2.id2(+)
and T1.id1=T3.id1(+)
and T1.id2=T3.id2(+)
...
and T1.id1=T9.id1(+)
and T1.id2=T9.id2(+)and T1.id1=T10.id1
and T1.id2=T10.id2
调试过了
select t1.id1,t1.id2,t1.shuliang,
t2.shuliang,
t3.shuliang,
t4.shuliang,
t5.shuliang,
t6.shuliang,
t7.shuliang,
t8.shuliang,
t9.shuliang,
t10.shuliang
from
codetable t1,
codetable t2,
codetable t3,
codetable t4,
codetable t5,
codetable t6,
codetable t7,
codetable t8,
codetable t9,
codetable t10
where
t1.id1=t2.id1
and t1.id1=t3.id1
and t1.id1=t4.id1
and t1.id1=t5.id1
and t1.id1=t6.id1
and t1.id1=t7.id1
and t1.id1=t8.id1
and t1.id1=t9.id1
and t1.id1=t10.id1
and t1.id2=t2.id2
and t1.id2=t3.id2
and t1.id2=t4.id2
and t1.id2=t5.id2
and t1.id2=t6.id2
and t1.id2=t7.id2
and t1.id2=t8.id2
and t1.id2=t9.id2
and t1.id2=t10.id2
and t2.shuliang >t1.shuliang
and t3.shuliang >t2.shuliang
and t4.shuliang >t3.shuliang
and t5.shuliang >t4.shuliang
and t6.shuliang >t5.shuliang
and t7.shuliang >t6.shuliang
and t8.shuliang >t7.shuliang
and t9.shuliang >t8.shuliang
and t10.shuliang >t9.shuliang
and t2.shuliang >t1.shuliang
and t3.shuliang >t2.shuliang
and t4.shuliang >t3.shuliang
and t5.shuliang >t4.shuliang
and t6.shuliang >t5.shuliang
and t7.shuliang >t6.shuliang
and t8.shuliang >t7.shuliang
and t9.shuliang >t8.shuliang
and t10.shuliang >t9.shuliang
就是如果有数字相等.....这个...完蛋
SELECT
id1,id2,
sum(CASE WHEN mod(rn,10) = 1 THEN shuliang END) AS shuliang1,
sum(CASE WHEN mod(rn,10) = 2 THEN shuliang END) AS shuliang2,
sum(CASE WHEN mod(rn,10) = 3 THEN shuliang END) AS shuliang3,
sum(CASE WHEN mod(rn,10) = 4 THEN shuliang END) AS shuliang4,
sum(CASE WHEN mod(rn,10) = 5 THEN shuliang END) AS shuliang5,
sum(CASE WHEN mod(rn,10) = 6 THEN shuliang END) AS shuliang6,
sum(CASE WHEN mod(rn,10) = 7 THEN shuliang END) AS shuliang7,
sum(CASE WHEN mod(rn,10) = 8 THEN shuliang END) AS shuliang8,
sum(CASE WHEN mod(rn,10) = 9 THEN shuliang END) AS shuliang9,
sum(CASE WHEN mod(rn,10) = 0 THEN shuliang END) AS shuliang10
FROM
(SELECT id1,id2,shuliang,ROWNUM AS rn FROM
(SELECT id1,id2,shuliang FROM codetable ORDER Bid2 id1,id2))
GROUP Bid2 id1,id2;
将里面的:Bid2改成By
就可以跑了,另外还有个前提,你的数据库要是oracle的