如何用SQL语句实现如下目的？
数据库：MS SQL 2000:
drop table mytest
go
create table mytest
(
sn int,
code varchar(8),
ins_no varchar(6),
)
go
insert mytest values(1, '01', 'A')
insert mytest values(1, '01', 'B')
insert mytest values(2, '02', 'A')
insert mytest values(2, '02', 'D')
insert mytest values(3, '01', 'A')
insert mytest values(3, '01', 'B')
insert mytest values(4, '03', 'A')
insert mytest values(4, '03', 'B')
insert mytest values(4, '03', 'C')
insert mytest values(5, '03', 'B')
insert mytest values(5, '03', 'C')
insert mytest values(6, '02', 'A')
insert mytest values(6, '02', 'C')
insert mytest values(6, '02', 'D')
go
select * from mytest
go
sn code ins_no
--- -------- ------
1 01 A
1 01 B
2 02 A
2 02 D
4 03 A
4 03 B
4 03 C
5 03 B
5 03 C
6 02 A
6 02 C
6 02 D
能否用一个查询语句得到？先谢了
我发现如果用很多NOT EXITS去写，将来代码的可读性不高，毕竟，要达到象LODGE这样对SQL熟悉的程度，并非易事，因此，我还是用SP实现了。
代码如下：
drop table mytest
go
create table mytest
(
sn int,
code varchar(8),
ins_no varchar(6),
)
go
insert mytest values(1, '01', 'A')
insert mytest values(1, '01', 'B')
insert mytest values(2, '02', 'A')
insert mytest values(2, '02', 'D')
insert mytest values(3, '01', 'A')
insert mytest values(3, '01', 'D')
insert mytest values(4, '03', 'A')
insert mytest values(4, '03', 'B')
insert mytest values(4, '03', 'C')
insert mytest values(5, '03', 'B')
insert mytest values(5, '03', 'C')
insert mytest values(6, '02', 'A')
insert mytest values(6, '02', 'C')
insert mytest values(6, '02', 'D')
insert mytest values(7, '01', 'A')
insert mytest values(7, '01', 'B')
insert mytest values(8, '03', 'C')
insert mytest values(8, '03', 'B')
go
drop proc Filter
go
create proc Filter
as
declare @iCurrentGroup int, @iMaxGroupNum int, @iRowCount int, @iTemp int
create table #temp
(
code varchar(8),
ins_no varchar(6),
)
select @iCurrentGroup = 1
select @iMaxGroupNum = max(sn) from mytest
while (@iCurrentGroup <= @iMaxGroupNum)
begin
-- 先将要判断的分组放到临时表
insert #temp (code, ins_no)
select code, ins_no
from mytest
where sn = @iCurrentGroup
select @iRowCount = @@ROWCOUNT
while (@iCurrentGroup <= @iMaxGroupNum)
begin
if (@iRowCount != 0)
begin
-- 比较
select @iTemp = @iCurrentGroup + 1
while (@iTemp <= @iMaxGroupNum)
begin
-- 必须满足的条件：两个表的纪录数相等，纪录完全相同
if ((select count(*) from mytest where sn = @iTemp)
= @iRowCount)
begin
if ((select count(*) from #temp a, (select code, ins_no from mytest where sn = @iTemp) b
where a.code = b.code and a.ins_no = b.ins_no ) = @iRowCount
-- 另一分组的纪录数等于前一分组的
and (select count(*) from mytest where sn = @iTemp)
= @iRowCount)
begin
--
delete mytest where sn = @iTemp
end
end
select @iTemp = @iTemp + 1
end
end
truncate table #temp
select @iCurrentGroup = @iCurrentGroup + 1
insert #temp (code, ins_no)
select code, ins_no
from mytest
where sn = @iCurrentGroup
select @iRowCount = @@ROWCOUNT
end
drop table #temp
end
go
exec Filter
select * from mytest
