问题描述如下:
1、现已通过一个非常复杂的条件可从SQL2000中出一个结果集(数据列为10列,数据行约20000条);
2、需要再次从这个结果集中查询出符合条件的记录:
1)、假设数据列标题分别以“A列、B列、C列、D列、E列、F列、G列、H列、I列、J列”表示;
2)、求结果集中:同时有C列、F列、H列都相同的记录(不是指C列=F列=H列);
1、现已通过一个非常复杂的条件可从SQL2000中出一个结果集(数据列为10列,数据行约20000条);
2、需要再次从这个结果集中查询出符合条件的记录:
1)、假设数据列标题分别以“A列、B列、C列、D列、E列、F列、G列、H列、I列、J列”表示;
2)、求结果集中:同时有C列、F列、H列都相同的记录(不是指C列=F列=H列);
(select c,f,h from tb group by c,f,h having count(1) > 1) n
where m.c = n.c and m.f = n.f and m.h = n.h
(select c,f,h from tb group by c,f,h having count(1) > 1) n
where m.c = n.c and m.f = n.f and m.h = n.h
支持
*
from
(结果集表) t
where
exists(select 1 from tb where c=t.c and f=t.f and h=t.h and a<>t.a)
(
A int,
B int,
C int,
D int,
E int,
F int,
G int,
H int
)
--C列、F列、H列
insert into #A select 1,2,3,44,5,6,7,81
insert into #A select 11,2,3,41,5,6,7,81
insert into #A select 11,2,45,40,5,6,7,81
insert into #A select 12,2,53,44,5,6,7,81
insert into #A select 13,2,3,46,5,6,7,81
insert into #A select 14,2,45,48,5,6,7,8
insert into #A select 15,2,38,49,5,6,7,8
insert into #A select 16,2,30,40,5,6,7,8select A1.* from #A A1 join
(
select C,F,H from #A group by C,F,H having count(C)>1
) A2
on A1.C=A2.C
and A1.F=A2.F
and A1.H=A2.H
A B C D E F G H
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2 3 44 5 6 7 81
11 2 3 41 5 6 7 81
13 2 3 46 5 6 7 81(3 行受影响)
用exists执行速度是不是更快些
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-18 11:36:20
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
-- Dec 16 2008 19:46:53
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int,[c] int)
insert [tb]
select 1,2,3 union all
select 2,3,4 union all
select 3,3,4 union all
select 4,2,3 union all
select 5,1,2 union all
select 6,2,2
--------------开始查询--------------------------
select
*
from
tb t
where
exists(select 1 from tb where c=t.c and b=t.b and a<>t.a)
----------------结果----------------------------
/* a b c
----------- ----------- -----------
1 2 3
2 3 4
3 3 4
4 2 3(所影响的行数为 4 行)
*/
select * from (select *,count(*) (partition by c,f,h) as num from tb) a
where num>1
(select C,F,H from #t group by C,F,H having count(C)>1) v
where a.C=b.C
and a.F=b.F
and a.H=b.H