解决方案 »
- db2 启动 SQL5043N 求助?
- Easyflow表结构
- 如何使用sql语句更新表
- 用dbca删除oracle10数据库,为什么不输入用户名和密码啊?
- 关于左外连接的效率问题
- 数据库连接中突然增加了许多的连接,其中许多的连接是来实现这个SQL语句的。大虾分析一下原因。
- [求助] ORA-04031: unable to allocate 4200 bytes of shared memory
- Pb6.5调用Oracle9i存储过程时,如何传入多个入参?
- 怎样把oracle 数据表转化为Access表和SQL Server 表 数据类型要注意那些?
- 怎么设置网络可以连接到虚拟机的Oracle?
- 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