SQL查询的严重问题,求解决办法!

[yanele]

问题描述如下:
1、现已通过一个非常复杂的条件可从SQL2000中出一个结果集（数据列为10列，数据行约20000条）；
2、需要再次从这个结果集中查询出符合条件的记录：
1）、假设数据列标题分别以“A列、B列、C列、D列、E列、F列、G列、H列、I列、J列”表示；
2）、求结果集中：同时有C列、F列、H列都相同的记录(不是指C列=F列=H列)；

[dawugui]

select m.* from tb m,
(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

[ws_hgo]

select * from tb having(count(C))>1 and having(count(F))>1 and having(count(H))>1

[jiangshun]

你想干嘛？

[yanele]

这么快!超出想像!验证后再结贴给分!

[ws_hgo]

select * from tb group by C,F,H having count(C)>1 and count(F))>1 and count(H)>1

[ws_hgo]

修改下用5楼的

[dawugui]

[Quote=引用 1 楼 dawugui 的回复:]
select m.* from tb m,
(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
[/Quote]
思路就是这个思路,你自己想想怎么整合进你的语句中.

[GUOCHENGJUN]

select m.* from tb m,
(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
支持

[--小F--]

select
  *
from
  (结果集表) t
where
  exists(select 1 from tb where c=t.c and f=t.f and h=t.h and a<>t.a)

[ws_hgo]

create table #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,8

select 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 行受影响)

[mail_ylei]

F姐
用exists执行速度是不是更快些

[--小F--]

----------------------------------------------------------------
-- 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 行）
*/

[东那个升]

2005 OVER（）方法。

select * from (select *,count(*) (partition by c,f,h)  as num from  tb) a
where num>1

[jwwyqs]

select a.* from #t a
(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