oracle数据
有一个表如下
用户帐号 选择人1 钱数1 时间1 选择人2 钱数2 时间2 …… 选择人10,钱数10,时间10
uerid eid1 mny1 dt1 eid2 mny2 dt2……eid10 mny10 dt10
0001 a1 100 2007-1-1 a2 100 2007-1-1 ……a7 100 2008-1-1
0002 a2 100 2006-1-1 a5 100 2006-1-1 ……a6 100 2008-1-1
0003 a3 100 2007-1-1 a9 100 2006-1-1 ……a2 100 2008-1-1实现下面的功能将eid1-eid10中a2找出来 显示userid和a2对应的mny和dt 并安装时间正序排列效果如下userid mny dt
0002 100 2006-1-1
0001 100 2007-1-1
0003 100 2008-1-1
有一个表如下
用户帐号 选择人1 钱数1 时间1 选择人2 钱数2 时间2 …… 选择人10,钱数10,时间10
uerid eid1 mny1 dt1 eid2 mny2 dt2……eid10 mny10 dt10
0001 a1 100 2007-1-1 a2 100 2007-1-1 ……a7 100 2008-1-1
0002 a2 100 2006-1-1 a5 100 2006-1-1 ……a6 100 2008-1-1
0003 a3 100 2007-1-1 a9 100 2006-1-1 ……a2 100 2008-1-1实现下面的功能将eid1-eid10中a2找出来 显示userid和a2对应的mny和dt 并安装时间正序排列效果如下userid mny dt
0002 100 2006-1-1
0001 100 2007-1-1
0003 100 2008-1-1
解决方案 »
- 新手学习,遇到问题
- 关于Oracle9i在Windows Server 2008系统上的安装
- 求一条sql语句
- 如何在Oracle数据库下建立作业!!急
- @_@ 怪事年年有,有没有谁遇见过不用的用户看同一张表时返回不同的记录数,如果有的话,能说说是什么原因吗?
- 表的列名是关键字,怎么建视图?
- 巨简单的问题
- 在国内做数据库项目,对于数据库字段名用 “英文”还是“拼音”标识好?等待
- 关于外键约束
- 请问这种错误如何避免?ORA-02292: 违反完整约束条件 (THTF.FK_ACADEMY_CERTIFICATIONTYPE) - 已找到子记录日
- Oracle如何分列显示结果集
- 不同数据库,用户资源之间的访问。
case when eid1 = 'a2' then mny1
when eid2 = 'a2' then mny2
when eid3 = 'a2' then mny3
when eid4 = 'a2' then mny4
when eid5 = 'a2' then mny5
when eid6 = 'a2' then mny6
when eid7 = 'a2' then mny7
when eid8 = 'a2' then mny8
when eid9 = 'a2' then mny9
when eid10 = 'a2' then mny10
else null
end,
dt
FROM tab
where eid1 = 'a2' or eid2 = 'a2' or eid3 = 'a2' or eid4 = 'a2' or eid5 = 'a2' or eid6 = 'a2' or eid7 = 'a2' or eid8 = 'a2' or eid9 = 'a2' or eid10 = 'a2'
order by dt;
case when eid1 = 'a2' then mny1
when eid2 = 'a2' then mny2
when eid3 = 'a2' then mny3
when eid4 = 'a2' then mny4
when eid5 = 'a2' then mny5
when eid6 = 'a2' then mny6
when eid7 = 'a2' then mny7
when eid8 = 'a2' then mny8
when eid9 = 'a2' then mny9
when eid10 = 'a2' then mny10
else null
end as mny,
case when eid1 = 'a2' then dt1
when eid2 = 'a2' then dt2
when eid3 = 'a2' then dt3
when eid4 = 'a2' then dt4
when eid5 = 'a2' then dt5
when eid6 = 'a2' then dt6
when eid7 = 'a2' then dt7
when eid8 = 'a2' then dt8
when eid9 = 'a2' then dt9
when eid10 = 'a2' then dt10
else null
end as dt
FROM tab
where eid1 = 'a2' or eid2 = 'a2' or eid3 = 'a2' or eid4 = 'a2' or eid5 = 'a2' or eid6 = 'a2' or eid7 = 'a2' or eid8 = 'a2' or eid9 = 'a2' or eid10 = 'a2'
order by dt;