update mom20100
set finished='1'
from (
select top 100 PERCENT refno,mom30100.type,mom30100.refseq, mom20100.gxh from mom30100,mom30101 ,mom20100
where mom30100.type<>'1' and mom30100.no = mom30101.no and mom30101.no>=@s_no and
mom30101.no<=@e_no and mom30100.refno = mom20100.momno and mom30100.refseq = mom20100.seq and mom30100.type = mom20100.type and mom30100.gxh= mom20100.gxh
group by mom30100.type,refno,mom30100.refseq,mom20100.num, mom20100.gxh
having sum(mom30101.num)>=mom20100.num
order by mom30100.type,refno,mom30100.refseq, mom20100.gxh)t
where t.refno = mom20100.momno and t.type = mom20100.type and t.refseq = mom20100.seq and finished='0' and t.gxh= mom20100.gxh以上语句在ORACLE中如何实现
set finished='1'
from (
select top 100 PERCENT refno,mom30100.type,mom30100.refseq, mom20100.gxh from mom30100,mom30101 ,mom20100
where mom30100.type<>'1' and mom30100.no = mom30101.no and mom30101.no>=@s_no and
mom30101.no<=@e_no and mom30100.refno = mom20100.momno and mom30100.refseq = mom20100.seq and mom30100.type = mom20100.type and mom30100.gxh= mom20100.gxh
group by mom30100.type,refno,mom30100.refseq,mom20100.num, mom20100.gxh
having sum(mom30101.num)>=mom20100.num
order by mom30100.type,refno,mom30100.refseq, mom20100.gxh)t
where t.refno = mom20100.momno and t.type = mom20100.type and t.refseq = mom20100.seq and finished='0' and t.gxh= mom20100.gxh以上语句在ORACLE中如何实现
解决方案 »
- 帮忙(怎进一步提高orcale)
- 各位大虾帮帮忙,在pl/sql developer中下列需求如何实现???????????????
- 两个库之间,相同的表的数据对拷问题!!
- 一个select问题
- 在oracle中如何取两个日期的天数差?
- 请问查询时怎么用“0”替代空值?
- 在线求助:存储过程中怎么定义一个数据库连接?
- 急:我把C:\oracle\oradata\TEST\下面的UNDOTBS01.DBF这个文件删除了,ORACLE不好用了.
- oracle+aix下的问题
- 如何将不同条件的group by放到一个select里面?
- 执行拼接字符串的问题
- oracle PACKAGE 和存储过程问题,这次不是应该不是分号问题了
from mom10100
where mom20100.momno = mom10100.no and mom20100.type = '2' and mom10100.ifzc='0' and mom10100.finished='1'这个如何实现
delete mom20100
where exists(select 1 from mom10100
where mom20100.momno = mom10100.no and mom20100.type = '2' and mom10100.ifzc='0' and mom10100.finished='1')
with t as (
select refno,mom30100.type,mom30100.refseq, mom20100.gxh
from mom30100,mom30101 ,mom20100
where mom30100.type<>'1' and mom30100.no = mom30101.no
and mom30101.no>=&s_no and mom30101.no<=&e_no
and mom30100.refno = mom20100.momno
and mom30100.refseq = mom20100.seq
and mom30100.type = mom20100.type and mom30100.gxh= mom20100.gxh
group by mom30100.type,refno,mom30100.refseq,mom20100.num, mom20100.gxh
having sum(mom30101.num)>=mom20100.num
)
update (select mom20100.* from t, mom20100
where t.refno = mom20100.momno and t.type = mom20100.type
and t.refseq = mom20100.seq and finished='0' and t.gxh= mom20100.gxh) m
set m.finished='1';
str = 'create table t as
select top 100 PERCENT refno,mom30100.type,mom30100.refseq, mom20100.gxh from mom30100,mom30101 ,mom20100
where mom30100.type<>'1' and mom30100.no = mom30101.no and mom30101.no>=@s_no and
mom30101.no<=@e_no and mom30100.refno = mom20100.momno and mom30100.refseq = mom20100.seq and mom30100.type = mom20100.type and mom30100.gxh= mom20100.gxh
group by mom30100.type,refno,mom30100.refseq,mom20100.num, mom20100.gxh
having sum(mom30101.num)>=mom20100.num
order by mom30100.type,refno,mom30100.refseq, mom20100.gxh'
execute immediate str;
delete mom20100
from mom10100
where mom20100.momno = mom10100.no and mom20100.type = '2' and mom10100.ifzc='0' and mom10100.finished='1'虽然我也好久不用SQL SERVER 2000了 不过用的时间还比较长 我想问下 这个语句是从MOM20100中删除数据吗 这个我还真记不清了 我理解应该是从MOM10100中吧
select top 15 percent 这个呢
select refno,mom30100.type,mom30100.refseq, mom20100.gxh
from mom30100,mom30101 ,mom20100
where mom30100.type<>'1' and mom30100.no = mom30101.no
and mom30101.no>=&s_no and mom30101.no<=&e_no
and mom30100.refno = mom20100.momno
and mom30100.refseq = mom20100.seq
and mom30100.type = mom20100.type and mom30100.gxh= mom20100.gxh
group by mom30100.type,refno,mom30100.refseq,mom20100.num, mom20100.gxh
having sum(mom30101.num)>=mom20100.num
order by mom30100.type,refno,mom30100.refseq, mom20100.gxh
),
t as(
select * from t where rownum/(select count(*) from t)<=0.15
)
update (select mom20100.* from t, mom20100
where t.refno = mom20100.momno and t.type = mom20100.type
and t.refseq = mom20100.seq and finished='0' and t.gxh= mom20100.gxh) m
set m.finished='1';
set finished='1'
where exists (select 1 from (
select top 100 PERCENT refno,mom30100.type,mom30100.refseq, mom20100.gxh from mom30100,mom30101 ,mom20100
where mom30100.type<>'1' and mom30100.no = mom30101.no and mom30101.no>=@s_no and
mom30101.no<=@e_no and mom30100.refno = mom20100.momno and mom30100.refseq = mom20100.seq and mom30100.type = mom20100.type and mom30100.gxh= mom20100.gxh
group by mom30100.type,refno,mom30100.refseq,mom20100.num, mom20100.gxh
having sum(mom30101.num)>=mom20100.num
order by mom30100.type,refno,mom30100.refseq, mom20100.gxh)t
where t.refno = mom20100.momno and t.type = mom20100.type and t.refseq = mom20100.seq and finished='0' and t.gxh= mom20100.gxh);