解决方案 »
- 在oracle 11g关于group by语句的用法?
- 请问:我想在imp命令时 我想log出日志文件
- oracle上亿条记录大表delete
- shell脚本求解
- oracle 数值保留小数点问题 在线等!
- EXPDP导出ORACLE数据库后,impdp导入另一个用户,查询数据库表时显示表或视图不存在
- 100分求在NT4SP6工作站上安装ORALCE的问题的解决办法??
- 求助:CrystalReport for VS.NET + Oracle开发报表时所遇到的问题,在线等,叩谢!!!(没有办法,级别低,分少!)
- 关于ORACLE与其他数据库转换的问题!!!
- Oracle8.17 For Solaris
- oracle树状查询,如何截取结果集的部分数据
- 如何设计论坛评论表呢?
where a.time=2014 and b.time=2013 and a.id=b.id
11g只有版本可以考虑使用pivot进行行转列
select id,
max(decode(time,2014,name)),
max(age),
max(decode(time,2013,name))
from a
where time in (2014,2013)
group by id
使用了这个可以,又出现新问题,要求time=2014时,才能查询结果来,该如何写
max(decode(time,2014,name)),
max(age),
max(decode(time,2013,name))
from a a1
where time=2014 or
(time=2013 and exists (select 1 from a where id=a1.id and time=2014))
group by id
或是采用1#的方法改为左连接的方式
select a.id,a.name,a.age,b.name from a ,a b
where a.time=2014 and b.time=2013 and a.id(+)=b.id
time=2013 后提示无效数字是为什么
把你报错的正式语句贴上来,如果time是数字型的话这么写没啥问题
max(a.item_name) as itemname,
max(getunitname(a.item_code)) as unitname,
max(decode(t.company_code,'0101',t.item_value)) as value1,
max(decode(t.company_code,'0102',t.item_value)) as value2,
max(decode(t.company_code,'0103',t.item_value)) as value3,
max(decode(t.company_code,'0104',t.item_value)) as value4,
max(decode(t.company_code,'0105',t.item_value)) as value5,
max(decode(t.company_code,'0106',t.item_value)) as value6,
sum(decode(t.budget_time,2014,t.item_value)) as ietmvalue,
sum(decode(t.budget_time,2013,t.item_value)) as lastietmvalue
from CBM_J_BUDGET_GATHERCOM t,CBM_C_ITEM a
where a.item_id = t.item_id
and t.budget_time ='2014'
and t.topic_id = '45'
or t.budget_time='2013'
and exists (select 1 from CBM_J_BUDGET_GATHERCOM tt,CBM_C_ITEM aa
where aa.item_id = tt.item_id and tt.topic_id = '45' and tt.budget_time ='2013')
group by t.item_id order by t.item_id 我现在用in(2013,2014)行了,不过还是想知道原因
select t.item_id,
max(a.item_name) as itemname,
max(getunitname(a.item_code)) as unitname,
max(decode(t.company_code,'0101',t.item_value)) as value1,
max(decode(t.company_code,'0102',t.item_value)) as value2,
max(decode(t.company_code,'0103',t.item_value)) as value3,
max(decode(t.company_code,'0104',t.item_value)) as value4,
max(decode(t.company_code,'0105',t.item_value)) as value5,
max(decode(t.company_code,'0106',t.item_value)) as value6,
sum(decode(t.budget_time,2014,t.item_value)) as ietmvalue,
sum(decode(t.budget_time,2013,t.item_value)) as lastietmvalue
from CBM_J_BUDGET_GATHERCOM t,CBM_C_ITEM a
where a.item_id = t.item_id
and t.topic_id = '45'
and (t.budget_time ='2014'
or (t.budget_time='2013'
and exists (select 1 from CBM_J_BUDGET_GATHERCOM
where item_id = t.item_id and topic_id = '45' and budget_time ='2014')))
group by t.item_id order by t.item_id
你的budget_time应该是字符串类型的吧 sum(decode(t.budget_time,'2014',t.item_value)) as ietmvalue,
sum(decode(t.budget_time,'2013',t.item_value)) as lastietmvalue