DELETE
FROM table_name a
WHERE rowid > ( SELECT min(rowid)
FROM table_name b
WHERE b.pk_column_1 = a.pk_column_1
and b.pk_column_2 = a.pk_column_2)
这个用法好象也不对吧~~
子查询中查出个各条重复记录的最小rowid,但主查询中应该是!=这些rowid啊
如果只是>这些rowid好象不对吧
FROM table_name a
WHERE rowid > ( SELECT min(rowid)
FROM table_name b
WHERE b.pk_column_1 = a.pk_column_1
and b.pk_column_2 = a.pk_column_2)
这个用法好象也不对吧~~
子查询中查出个各条重复记录的最小rowid,但主查询中应该是!=这些rowid啊
如果只是>这些rowid好象不对吧
select id from student
group by id having count(*) > 1-根据查找出的id,可以删除最大或者是最小id的
delete from test a where a.rowid!=(select max(rowid) from test b where a.a=b.a and a.b=b.b and a.c=b.c);
delete from test where rowid not in(select max(rowid) from test group by a,b)
---------------------------------------------
在表中插入记录的时候,让主键自动产生——序列的应用
drop table foo;
create table foo(
id varchar2(20) primary key,
data varchar2(100));
create sequence foo_seq;
create or replace trigger bifer_foo_id_pk
before insert
on foo
for each row
begin
select to_char(foo_seq.nextval) into :new.id from dual;
end;
/
insert into foo(data)
values('Chirstopher');
insert into foo(id,data)
values(5,'Sean');
select * from foo;
---------------------------------------------
选出表中间某段——关于rownum
select * from test where rownum < m+n
minus
select * from test where rownum > m
---------------------------------------------
多表连接随机抽取N行
select * from (select * from tablename order by dbms_random.value) where rownum< N;
---------------------------------------------
查系统日期格式
SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
---------------------------------------------
用SQL语句修改表中字段的名字
ALTER TABLE test RENAME COLUMN oldc TO newc;
where rowid=(select max(rowid) from tsm_staff_role
where staffid in (select staffid from tsm_staff_role
group by staffid having count(staffid) > 1))
各位大侠看看我这个删除怎么不可以?
好在今天休息,请看...
-- << 1. Create test table TB1
CREATE TABLE TB1
(
ID NUMBER(2),
STR VARCHAR2(10 BYTE)
);-- << 2. Insert test data
INSERT INTO TB1 (ID, STR) VALUES (1, 'str1_1');
INSERT INTO TB1 (ID, STR) VALUES (2, 'str2_1');
INSERT INTO TB1 (ID, STR) VALUES (1, 'str1_2');
INSERT INTO TB1 (ID, STR) VALUES (2, 'str2_2');
INSERT INTO TB1 (ID, STR) VALUES (3, 'str1_3');
COMMIT;-- << 3. Select all of the date
SELECT * FROM TB1 ORDER BY id;-- << it should displayed like
/*
ID STR
---------- ----------
1 str1_1
1 str1_2
1 str1_3
2 str2_1
2 str2_2 5 rows selected.
*/-- << 4. Show all the duplicate data
SELECT * FROM TB1 WHERE ROWID IN
(SELECT A1.ROWID
FROM TB1 A1
WHERE EXISTS (SELECT 'x' FROM TB1 A2
WHERE A1.ID = A2.ID
AND A1.ROWID <> A2.ROWID));-- << it should displayed like
/*
ID STR
---------- ----------
1 str1_1
2 str2_1
1 str1_2
2 str2_2
1 str1_3 5 rows selected.
*/-- << 5. Show the duplicate date except first row
SELECT * FROM TB1 WHERE ROWID IN
(SELECT A1.ROWID
FROM TB1 A1
WHERE EXISTS (SELECT 'x' FROM TB1 A2
WHERE A1.ID = A2.ID
AND A1.ROWID > A2.ROWID));-- << it should displayed like
/*
ID STR
---------- ----------
1 str1_2
2 str2_2
1 str1_3 3 rows selected.
*/-- << 6. Delete duplicate rows -- <<
DELETE FROM TB1 WHERE ROWID IN
(SELECT A1.ROWID
FROM TB1 A1
WHERE EXISTS (SELECT 'x' FROM TB1 A2
WHERE A1.ID = A2.ID
AND A1.ROWID <> A2.ROWID));-- << delete all duplicate rows except the row with the min rowid
DELETE FROM TB1 WHERE ROWID IN
(SELECT A1.ROWID
FROM TB1 A1
WHERE EXISTS (SELECT 'x' FROM TB1 A2
WHERE A1.ID = A2.ID
AND A1.ROWID > A2.ROWID));-- << delete all duplicate rows except the row with the max rowid
DELETE FROM TB1 WHERE ROWID IN
(SELECT A1.ROWID
FROM TB1 A1
WHERE EXISTS (SELECT 'x' FROM TB1 A2
WHERE A1.ID = A2.ID
AND A1.ROWID > A2.ROWID));
-- << 6. Delete duplicate rows -- << Delete all of the duplicate rows
DELETE FROM TB1 WHERE ROWID IN
(SELECT A1.ROWID
FROM TB1 A1
WHERE EXISTS (SELECT 'x' FROM TB1 A2
WHERE A1.ID = A2.ID
AND A1.ROWID <> A2.ROWID));-- << delete all duplicate rows except the row with the min rowid
DELETE FROM TB1 WHERE ROWID IN
(SELECT A1.ROWID
FROM TB1 A1
WHERE EXISTS (SELECT 'x' FROM TB1 A2
WHERE A1.ID = A2.ID
AND A1.ROWID > A2.ROWID));-- << delete all duplicate rows except the row with the max rowid
DELETE FROM TB1 WHERE ROWID IN
(SELECT A1.ROWID
FROM TB1 A1
WHERE EXISTS (SELECT 'x' FROM TB1 A2
WHERE A1.ID = A2.ID
AND A1.ROWID < A2.ROWID));
if object_id('tempdb..#tmp') is not null
drop table #tmp
select distinct * into #tmp from tt
truncate table tt
insert into tt select * from #tmp方法二:添加标识列
alter table tt add NewID int identity(1,1)
go
delete from tt where exists(select 1 from tt a where a.newid>tt.newid and tt.id=a.id and tt.pid=a.pid)
go
alter table tt drop column NewID
go