在线急等一SQL语句

[victory610]

我有一个表，结构如下：
num1 num2 num3 num4 num5
1 2 3 4 5
1 2 3 5 6
1 2 7 4 5
2 3 4 5 6
2 3 4 7 8
2 3 4 8 9
2 3 5 6 7

现在我想得到以下结果：
num1 num2 num3 num4 num5
2 3 4 5 6
2 3 4 7 8
2 3 4 8 9

也就是要查询出有三行或三行以上，它们的每一行的num1，num2，num3值对应相等，请高手指教。

[水族杰纶]

--> Title  : Generating test data 
--> Author : wufeng4552
--> Date   : 2010-01-26 14:47:21
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([num1] int,[num2] int,[num3] int,[num4] int,[num5] int)
Insert tb
select 1,2,3,4,5 union all
select 1,2,3,5,6 union all
select 1,2,7,4,5 union all
select 2,3,4,5,6 union all
select 2,3,4,7,8 union all
select 2,3,4,8,9 union all
select 2,3,5,6,7
Go
Select a.* from tb a
,(select [num1],[num2],[num3] from tb group by [num1],[num2],[num3] having count(*)>=3)
b
where a.[num1]=b.[num1] and a.[num2]=b.[num2] and a.[num3]=b.[num3]
/*
num1        num2        num3        num4        num5
----------- ----------- ----------- ----------- -----------
2           3           4           5           6
2           3           4           7           8
2           3           4           8           9

(3 個資料列受到影響)

*/

[Mr_Nice]

--> 测试数据：[TT]
if object_id('[TT]') is not null drop table [TT]
create table [TT]([num1] int,[num2] int,[num3] int,[num4] int,[num5] int)
insert [TT]
select 1,2,3,4,5 union all
select 1,2,3,5,6 union all
select 1,2,7,4,5 union all
select 2,3,4,5,6 union all
select 2,3,4,7,8 union all
select 2,3,4,8,9 union all
select 2,3,5,6,7

select * from [TT]
--=================
select  Tb1.*
from TT Tb1 
inner join (select num1,num2,num3 from TT group by num1,num2,num3 having count(1)>=3) Tb2 
on Tb1.num1 = Tb2.num1 and Tb1.num2 = Tb2.num2 and Tb1.num3 = Tb2.num3

--=================
/*
num1	num2	num3	num4	num5
2	3	4	5	6
2	3	4	7	8
2	3	4	8	9
*/

[Mr_Nice]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL code--> Title : Generating test data
--> Author : wufeng4552
--> Date : 2010-01-26 14:47:21ifnotobject_id('tb')isnulldroptable tbGoCreatetable tb([num1]int,[num2]int,[num3]int,[num4]int,[num5]i?-
[/Quote]

水哥真快啊！

[ming_Y]

学习!

[jack15850798154]

帮顶！！！学习中。。。

[nianran520]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([num1] int,[num2] int,[num3] int,[num4] int,[num5] int)
go
insert [tb]
select 1,2,3,4,5 union all
select 1,2,3,5,6 union all
select 1,2,7,4,5 union all
select 2,3,4,5,6 union all
select 2,3,4,7,8 union all
select 2,3,4,8,9 union all
select 2,3,5,6,7

select * from [tb] t
where ltrim(num1)+ltrim(num2)+ltrim(num3) in 
(select ltrim(num1)+ltrim(num2)+ltrim(num3) from tb
group by ltrim(num1)+ltrim(num2)+ltrim(num3) having count(1) >=3)

--------------------------------
2	3	4	5	6
2	3	4	7	8
2	3	4	8	9

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-26 14:49:27
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([num1] int,[num2] int,[num3] int,[num4] int,[num5] int)
insert [tb]
select 1,2,3,4,5 union all
select 1,2,3,5,6 union all
select 1,2,7,4,5 union all
select 2,3,4,5,6 union all
select 2,3,4,7,8 union all
select 2,3,4,8,9 union all
select 2,3,5,6,7
--------------开始查询--------------------------
select
  t.*
from
  tb t
join
  (select num1,num2,num3 from tb group by num1,num2,num3 having count(1)>=3)b
on
  b.num1=t.num1 and b.num2=t.num2 and b.num3=t.num3
where
  exists(select 1 from tb where num1=t.num1 and num2=t.num2 and num3=t.num3 and num4<>t.num4 and num5<>t.num5)

----------------结果----------------------------
/*num1        num2        num3        num4        num5
----------- ----------- ----------- ----------- -----------
2           3           4           5           6
2           3           4           7           8
2           3           4           8           9

(3 行受影响) 
*/

[水族杰纶]

[Quote=引用 7 楼 fredrickhu 的回复:]
SQL code----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2010-01-26 14:49:27
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00?-
[/Quote]
加
exists
意義?

[SQL77]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([num1] int,[num2] int,[num3] int,[num4] int,[num5] int)
Insert tb
select 1,2,3,4,5 union all
select 1,2,3,5,6 union all
select 1,2,7,4,5 union all
select 2,3,4,5,6 union all
select 2,3,4,7,8 union all
select 2,3,4,8,9 union all
select 2,3,5,6,7

SELECT * FROM TB T
WHERE 
(SELECT COUNT(*) FROM TB 
WHERE 
NUM1=T.NUM1 AND NUM2=T.NUM2 
AND NUM3=T.NUM3 AND NUM4<=T.NUM4)>=3


（所影响的行数为 7 行）

num1        num2        num3        num4        num5        
----------- ----------- ----------- ----------- ----------- 
2           3           4           8           9

（所影响的行数为 1 行）

[jenny0810]

学习

[SQL77]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([num1] int,[num2] int,[num3] int,[num4] int,[num5] int)
Insert tb
select 1,2,3,4,5 union all
select 1,2,3,5,6 union all
select 1,2,7,4,5 union all
select 2,3,4,5,6 union all
select 2,3,4,7,8 union all
select 2,3,4,8,9 union all
select 2,3,5,6,7

SELECT T.* FROM TB T,
(
SELECT * FROM TB T
WHERE 
(SELECT COUNT(*) FROM TB 
WHERE NUM1=T.NUM1 AND NUM2=T.NUM2 AND NUM3=T.NUM3 AND NUM4<=T.NUM4)>=3)T1
WHERE T1.NUM1=T.NUM1 AND T1.NUM2=T.NUM2 AND T1.NUM3=T.NUM3



（所影响的行数为 7 行）

num1        num2        num3        num4        num5        
----------- ----------- ----------- ----------- ----------- 
2           3           4           5           6
2           3           4           7           8
2           3           4           8           9

（所影响的行数为 3 行）