语句 create or replace view v_rep_coil_info as select distinct(coil_sid) , en_weight , shift_crew , shift_no ,pro_day from v_ffff order by prod_day desc,coil_sid desc; 显示相同的coil_sid号?
create or replace view v_rep_coil_info as select distinct coil_sid, , en_weight , shift_crew , shift_no ,pro_day from v_ffff order by prod_day desc,coil_sid desc
在 (coil_sid)后加个,就成? 我试试
你要干嘛?是coil_sid相同的只显示一个coil_sid? 给个你想要的结果集
想要的就是在coil_sid那个字段下不能显示相同的号,现在能显示相同的号
现在的效果 coil_sid en_weight shift_crew pro_day 11111 12121 a 2010.03.09 22222 21212 a 2010.03.09 22222 21212 b 2010.03.10 33333 21211 b 2010.03.09 在coil_sid中有相同的号码.想要的是不管后面的是什么状况,只要coil_sid中的号码不能重复,后面的保留最早时间的就成
select coil_sid,en_weight,shift_crew,pro_day from v_ffff t where (t.coil_sid,t.pro_day) in (select coil_sid,min(pro_day) from v_ffff group by coil_sid)
distinct对后面所有字段均起作用, select distinct(coil_sid) , en_weight , shift_crew , shift_no ,pro_day from v_ffff 就是slect disctinct coil_sid,en_weight,shift_crew,shift_no,pro_day from v_ffff;
这样的不成,在相同的pro_day时间内有coil_sid的重复号出现.coil_sid en_weight shift_crew pro_day 11111 12121 a 2010.03.09 22222 21212 a 2010.03.09 22222 21212 b 2010.03.10 33333 21211 b 2010.03.10 33333 21211 b 2010.03.10想要的效果是 coil_sid en_weight shift_crew pro_day 11111 12121 a 2010.03.09 22222 21212 a 2010.03.09 33333 21211 b 2010.03.10
select coil_sid,en_weight,shift_crew,pro_day from v_ffff where (coil_sid,pro_day) in (select coil_sid,min(pro_day) from v_ffff t where t.rowid in(select min(tab.rowid) from v_ffff tab group by tab.coil_sid, tab.pro_day) group by coil_sid) 实际上照你这么说,我现在觉得这个数据是有点问题的了,因为你的要求是“只要coil_sid中的号码不能重复,后面的保留最早时间”,然而这个表里面有一些数据是coil_sid相同,pro_day也相同的。
select coil_sid,en_weight,shift_crew,pro_day from v_ffff a where (coil_sid,pro_day,a.rowid) in (select coil_sid,min(pro_day),min(t.rowid) from v_ffff t where t.rowid in(select min(tab.rowid) from v_ffff tab group by tab.coil_sid, tab.pro_day) group by coil_sid) 上面的不对,起码要改成这样……
create or replace view v_rep_coil_info as select distinct(coil_sid) , en_weight , shift_crew , shift_no ,pro_day from v_ffff order by prod_day desc,coil_sid desc; 用这个的话,会产生coil_sid相同,en_weight , shift_crew , shift_no ,pro_day不同的数据,不知道要怎么能该成在coil_sid中重复的话也只显示一条的数据
这就是数据库设计不遵守范式的后果啊………… coil_sid相同时,均取任意一条?
WHERE (coil_sid, prod_day, a.ROWID) IN ( SELECT coil_sid, MIN (prod_day), MIN (a.ROWID) FROM v_shift_rep t WHERE t.ROWID IN ( SELECT MIN (tab.ROWID) FROM v_shift_rep tab GROUP BY tab.coil_sid, tab.prod_day) GROUP BY coil_sid) ORDER BY prod_day DESC, coil_sid DESC;报错 编译不了
cannot select rowid from,or sample,a join view without a key-preserved table 编译时报这个错误
对coil_sid相同时取哪个都一样
create or replace view v_rep_coil_info as select coil_sid,en_weight,shift_crew,shift_no,pro_day from v_ffff where rowid in (select max(rowid) from v_ffff group by coil_sid) order by prod_day desc,coil_sid desc;
奇怪了,在我这边是好的:(oracle11g) SQL> create or replace view aaa as 2 select a,b 3 from aa a 4 where (a,b,a.rowid) in (select a,min(b),min(t.rowid) 5 from aa t where t.rowid in(select m in(tab.rowid) 6 fr om aa tab 7 gro up by tab.a, tab.b) 8 group by a);视图已创建。SQL> select * from aaa;A B -------------------- -------------------- 22222 1 11111 1 33333 1SQL> 查了一下你的这个错误 ORA-01445: cannot select ROWID from a join view without a key-preserved table Cause: A SELECT statement attempted to select ROWIDs from a view derived from a join operation. Because the rows selected in the view do not correspond to underlying physical records, no ROWIDs can be returned. Action: Remove ROWID from the view selection clause, then re-execute the statement.看看能不能在这个表里面找到其他的Unique字段,或主键字段,把这个查询里面的rowid换掉试试。
话说后来想了一下,不需要这么麻烦,也许这样就行了: create or replace view v_rep_coil_info as select ta.coil_sid, ta.pro_day, en_weight, shift_crew, shift_no from (select coil_sid,min(pro_day) pro_day from v_ffff group by coil_sid) ta, v_ffff tb where ta.coil_sid=tb.coil_sid and ta.pro_day=tb.pro_day;
试试这个create or replace view v_rep_coil_info as select coil_sid ,en_weight ,shift_crew ,shift_no ,pro_day from v_ffff where not exists (select 'X' from v_ffff f2 where f2.coil_sid=v_ffff.coil_sid and ( f2.pro_day>v_ffff.pro_day or ( f2.pro_day=v_ffff.pro_day and f2.rowid>v_ffff.rowid ) ) )
order by prod_day desc,coil_sid desc;
更改rowid 就可以了别的方法试过了不行 WHERE (coil_sid, prod_day, a.pcoil_sid) IN ( SELECT coil_sid, MIN (prod_day), MIN (a.pcoil_sid) FROM v_shift_rep t WHERE t.pcoil_sid IN ( SELECT MIN (tab.pcoil_sid) FROM v_shift_rep tab GROUP BY tab.coil_sid, tab.prod_day) 这样就可以了,非常感谢大家对我的帮助了
as
select
distinct coil_sid,
, en_weight
, shift_crew
, shift_no
,pro_day
from v_ffff
order by prod_day desc,coil_sid desc
我试试
给个你想要的结果集
coil_sid en_weight shift_crew pro_day
11111 12121 a 2010.03.09
22222 21212 a 2010.03.09
22222 21212 b 2010.03.10
33333 21211 b 2010.03.09
在coil_sid中有相同的号码.想要的是不管后面的是什么状况,只要coil_sid中的号码不能重复,后面的保留最早时间的就成
from v_ffff t
where (t.coil_sid,t.pro_day) in (select coil_sid,min(pro_day)
from v_ffff
group by coil_sid)
select
distinct(coil_sid)
, en_weight
, shift_crew
, shift_no
,pro_day
from v_ffff 就是slect disctinct coil_sid,en_weight,shift_crew,shift_no,pro_day from v_ffff;
11111 12121 a 2010.03.09
22222 21212 a 2010.03.09
22222 21212 b 2010.03.10
33333 21211 b 2010.03.10
33333 21211 b 2010.03.10想要的效果是
coil_sid en_weight shift_crew pro_day
11111 12121 a 2010.03.09
22222 21212 a 2010.03.09
33333 21211 b 2010.03.10
描述下你的详细业务规则?
33333的那个是在上个表中能产生多个重复的,我门是钢厂,coil_sid是个原料卷号,一个原料卷可以生成多个成品卷,所以有多个33333,33333这个需要一次塞选
select coil_sid,en_weight,shift_crew,pro_day
from v_ffff
where (coil_sid,pro_day) in (select coil_sid,min(pro_day)
from v_ffff t where t.rowid in(select min(tab.rowid)
from v_ffff tab
group by tab.coil_sid, tab.pro_day)
group by coil_sid)
实际上照你这么说,我现在觉得这个数据是有点问题的了,因为你的要求是“只要coil_sid中的号码不能重复,后面的保留最早时间”,然而这个表里面有一些数据是coil_sid相同,pro_day也相同的。
select coil_sid,en_weight,shift_crew,pro_day
from v_ffff a
where (coil_sid,pro_day,a.rowid) in (select coil_sid,min(pro_day),min(t.rowid)
from v_ffff t where t.rowid in(select min(tab.rowid)
from v_ffff tab
group by tab.coil_sid, tab.pro_day)
group by coil_sid)
上面的不对,起码要改成这样……
as
select
distinct(coil_sid)
, en_weight
, shift_crew
, shift_no
,pro_day
from v_ffff
order by prod_day desc,coil_sid desc; 用这个的话,会产生coil_sid相同,en_weight , shift_crew , shift_no ,pro_day不同的数据,不知道要怎么能该成在coil_sid中重复的话也只显示一条的数据
这就是数据库设计不遵守范式的后果啊…………
coil_sid相同时,均取任意一条?
( SELECT coil_sid, MIN (prod_day), MIN (a.ROWID)
FROM v_shift_rep t
WHERE t.ROWID IN
( SELECT MIN (tab.ROWID)
FROM v_shift_rep tab
GROUP BY tab.coil_sid, tab.prod_day)
GROUP BY coil_sid)
ORDER BY prod_day DESC, coil_sid DESC;报错 编译不了
编译时报这个错误
as
select coil_sid,en_weight,shift_crew,shift_no,pro_day from v_ffff
where rowid in
(select max(rowid) from v_ffff group by coil_sid)
order by prod_day desc,coil_sid desc;
奇怪了,在我这边是好的:(oracle11g)
SQL> create or replace view aaa as
2 select a,b
3 from aa a
4 where (a,b,a.rowid) in (select a,min(b),min(t.rowid)
5 from aa t where t.rowid in(select m
in(tab.rowid)
6 fr
om aa tab
7 gro
up by tab.a, tab.b)
8 group by a);视图已创建。SQL> select * from aaa;A B
-------------------- --------------------
22222 1
11111 1
33333 1SQL>
查了一下你的这个错误
ORA-01445: cannot select ROWID from a join view without a key-preserved table
Cause: A SELECT statement attempted to select ROWIDs from a view derived from a join operation. Because the rows selected in the view do not correspond to underlying physical records, no ROWIDs can be returned.
Action: Remove ROWID from the view selection clause, then re-execute the statement.看看能不能在这个表里面找到其他的Unique字段,或主键字段,把这个查询里面的rowid换掉试试。
create or replace view v_rep_coil_info as
select ta.coil_sid,
ta.pro_day,
en_weight,
shift_crew,
shift_no
from (select coil_sid,min(pro_day) pro_day
from v_ffff
group by coil_sid) ta,
v_ffff tb
where ta.coil_sid=tb.coil_sid
and ta.pro_day=tb.pro_day;
as
select
coil_sid
,en_weight
,shift_crew
,shift_no
,pro_day
from v_ffff
where not exists (select 'X'
from v_ffff f2
where f2.coil_sid=v_ffff.coil_sid
and ( f2.pro_day>v_ffff.pro_day
or ( f2.pro_day=v_ffff.pro_day
and f2.rowid>v_ffff.rowid
)
)
)
order by prod_day desc,coil_sid desc;
WHERE (coil_sid, prod_day, a.pcoil_sid) IN
( SELECT coil_sid, MIN (prod_day), MIN (a.pcoil_sid)
FROM v_shift_rep t
WHERE t.pcoil_sid IN
( SELECT MIN (tab.pcoil_sid)
FROM v_shift_rep tab
GROUP BY tab.coil_sid, tab.prod_day)
这样就可以了,非常感谢大家对我的帮助了