table:
order_no, order_case, case_status
111 222 B
111 333 A
122 444 A
122 555 A
133 666 B
144 777 A
想创建一个view
结果如下:
order_no, , case_status
111 B
122 A
133 B
144 A试图需求如下;
对于同一个order_no ,如果在表里有B,则在视图里显示B,如果没有B,只有A就显示A 请问这个create view的sql 怎么写?
order_no, order_case, case_status
111 222 B
111 333 A
122 444 A
122 555 A
133 666 B
144 777 A
想创建一个view
结果如下:
order_no, , case_status
111 B
122 A
133 B
144 A试图需求如下;
对于同一个order_no ,如果在表里有B,则在视图里显示B,如果没有B,只有A就显示A 请问这个create view的sql 怎么写?
解决方案 »
- Oracle 11g 启动sql developer 时提示enter full pathname of java.exe
- 哪位兄弟有《ORACLE高性能SQL调整》中的access.sql,access_report.sql等脚本啊?找了好久都没找到啊。
- 安装oracle的问题
- Group By,Distinct给搞晕了,之Oracle的一个查询问题!!
- SQL序列问题,请进请进
- 关于SQLpuls的使用(初学者)
- 这个动态游标的执行sql怎么写
- WIN98能安装ORACLE9吗?
- 怎么理解层次查询
- 如何在oracle后台数据库级的触发器中获得用户对数据库发出的每条sql语句?
- 初学着请教:触发器编译老出错。
- 小弟求救:oracle 和 eclipse 连接时需加的包有哪些? 谢!!!
AS
SELECT ORDER_NO,
MAX(DECODE(CASE_STATUS,'B','B','A'))
FROM TABLE T1
GROUP BY ORDER_NO;
table:
order_no, order_case, case_status
111 222 B
111 333 A
122 444 A
122 555 A
133 666 B
144 777 A
想创建一个view
结果如下:
order_no, order_case, case_status
111 222 B
111 333 B
122 444 A
122 555 A
133 666 B
144 777 A
AS
select order_no,max(case_status) from table group by order_no
;
CREATE OR repalce VIEW v_xxx
SELECT order_no,MAX(case_status)
FROM tableA
GROUP BY order_no
AS
select order_no,order_case,(select Max(DECODE(CASE_STATUS,'B','B','A')) from table where order_no = t.order_no)
from table t
;如果是求最大的則改為Max(CASE_STATUS),隻有兩種則用DECODE(CASE_STATUS,'B','B','A')
create or replace view test1 as
select t.order_no, t.order_case, t1.case_status
from test t,
(select order_no, max(case_status) case_status
from test
group by order_no) t1
where t.order_no = t1.order_no
create table test(order_no number,order_case number,case_status varchar2(1));
insert into test values(111,222,'B');
insert into test values(111,333,'A');
insert into test values(122,444,'A');
insert into test values(122,555,'A');
insert into test values(133,666,'B');
insert into test values(144,777,'A');
commit;
create or replace view test1 as
select t.order_no, t.order_case,(select decode(max(case_status),'B',max(case_status),'A') from test t1 where t1.order_no=t.order_no)
from test t