我的比赛表A
ID name1 name2 比赛时间
1 A B 2007-09-04
2 B A 2007-09-04
3 A C 2007-09-03
4 C A 2007-09-03
5 B D 2007-09-02
6 D B 2007-09-02
我现在只想保留
2 B A 2007-09-04
4 C A 2007-09-03
6 D B 2007-09-02因为A->B和B->A是同一场比赛,只要保留一条记录就行啦
请问这个sql怎么写呀?
ID name1 name2 比赛时间
1 A B 2007-09-04
2 B A 2007-09-04
3 A C 2007-09-03
4 C A 2007-09-03
5 B D 2007-09-02
6 D B 2007-09-02
我现在只想保留
2 B A 2007-09-04
4 C A 2007-09-03
6 D B 2007-09-02因为A->B和B->A是同一场比赛,只要保留一条记录就行啦
请问这个sql怎么写呀?
ID name1 name2 比赛时间
1 A B 2007-09-04
2 B A 2007-09-04
3 A C 2007-09-03
4 C A 2007-09-03DELETE A
FROM 表A A
WHERE EXISTS(
SELECT * FROM 表A
WHERE 比赛日期 = A.比赛日期
AND id > A.id
AND name1 = A.name2
AND name2 = A.name2)
t
from
A t
where
exists(select 1 from A where name1=t.name2 and name2=t.name1 and 比赛时间=t.比赛时间 and id>t.id)
WHERE EXISTS( SELECT * FROM 表A
WHERE 比赛日期 = A.比赛日期
AND id > A.id AND
((name1 = A.name1 AND name2 = A.name2) or
(name2 = A.name1 AND name1 = A.name2)))
from
A t1,A t2
where
t1.name1=t2.name2
and
t1.name2=t2.name1
and
t1.比赛时间=t2.比赛时间
and
t1.id<t2.id
(
select distinct * from
(
select name1,name2 from a
union all
select name2 name1,name1 name2 from a
)
) b
where a.name1 = b.name1 and a.name2 = b.name2
delete 表A where ID%2=1
Delete T From A As T Where Exists
(Select 1 From A Where name1=T.name2 And name2=T.name1 And 比赛时间=T.比赛时间 And ID>T.ID)
---保留ID小的那条
Delete T From A As T Where Exists
(Select 1 From A Where name1=T.name2 And name2=T.name1 And 比赛时间=T.比赛时间 And ID<T.ID)
insert into a values(1, 'A', 'B', '2007-09-04')
insert into a values(2, 'B', 'A', '2007-09-04')
insert into a values(3, 'A', 'C', '2007-09-03')
insert into a values(4, 'C', 'A', '2007-09-03')
insert into a values(5, 'B', 'D', '2007-09-02')
insert into a values(6, 'D', 'B', '2007-09-02')
go
delete from a where name1>name2
select * from a
drop table a/*
ID name1 name2 比赛时间
----------- ----- ----- ----------
1 A B 2007-09-04
3 A C 2007-09-03
5 B D 2007-09-02(所影响的行数为 3 行)
*/
insert into a values(1, 'A', 'B', '2007-09-04')
insert into a values(2, 'B', 'A', '2007-09-04')
insert into a values(3, 'A', 'C', '2007-09-03')
insert into a values(4, 'C', 'A', '2007-09-03')
insert into a values(5, 'B', 'D', '2007-09-02')
insert into a values(6, 'D', 'B', '2007-09-02')
go
delete from a where name1<name2
select * from a
drop table a/*
ID name1 name2 比赛时间
----------- ----- ----- ----------
2 B A 2007-09-04
4 C A 2007-09-03
6 D B 2007-09-02(所影响的行数为 3 行)
*/
FROM t a
WHERE EXISTS(
SELECT * FROM t
WHERE 比赛日期 = a.比赛日期
AND id > a.id
AND name1 = a.name2
AND name2 = a.name2)
FROM t a
WHERE EXISTS(
SELECT * FROM t
WHERE 比赛日期 = a.比赛日期
AND id > a.id
AND name1 = a.name2
AND name2 = a.name2)
这样可以不用改之前别的应用...
你是不是有查看本地比赛之类的.
.如果删除了就要改排序之类的....
要不干脆在最初就不插近来借前边的兄弟们的一用
create table a(ID int,name1 varchar(1),name2 varchar(1),比赛时间 varchar(10))
insert into a values(1, 'A', 'B', '2007-09-04')
insert into a values(2, 'B', 'A', '2007-09-04')
insert into a values(3, 'A', 'C', '2007-09-03')
insert into a values(4, 'C', 'A', '2007-09-03')
insert into a values(5, 'B', 'D', '2007-09-02')
insert into a values(6, 'D', 'B', '2007-09-02')
insert into a values(7, 'C', 'B', '2007-09-02')
go
select * from a where exists
(
select * from a as b
where a.name1 = b.name2
and b.name1 = a.name2
and a.name1 < a.name2)union
select * from a where not exists
(select * from a as b
where a.name1 = b.name2
and b.name1 = a.name2)
1 A B 2007-09-04
3 A C 2007-09-03
5 B D 2007-09-02
7 C B 2007-09-02
create table #(ID int,name1 varchar(1),name2 varchar(1),比赛时间 varchar(10))
insert into # values(1, 'A', 'B', '2007-09-04')
insert into # values(2, 'B', 'A', '2007-09-04')
insert into # values(3, 'A', 'C', '2007-09-03')
insert into # values(4, 'C', 'A', '2007-09-03')
insert into # values(5, 'B', 'D', '2007-09-02')
insert into # values(6, 'D', 'B', '2007-09-02')
godelete a from # a
where exists(select 1 from # where a.name1=name2 and a.name2=name1 and a.比赛时间=比赛时间 and a.id<id)select * from #ID name1 name2 比赛时间
----------- ----- ----- ----------
2 B A 2007-09-04
4 C A 2007-09-03
6 D B 2007-09-02(3 行受影响)