要查询Begin与End字段都相同的记录，出现次数在n次以上的结果集
有一个数据库表，结构为：（3个字段）
Begin 从 字符型
End 到 字符型
Time 时间 时间型
数据：
Begin End Time
111 222 2002-10-10
111 333 2002-10-11
111 444 2002-10-12
111 222 2002-10-13
111 222 2002-10-14
111 333 2002-10-15
222 111 2002-10-16
222 333 2002-10-17
222 555 2002-10-18
444 222 2002-10-19
444 222 2002-10-20
666 222 2002-10-21
要查询Begin与End字段都相同的记录，出现次数在n次以上的结果集
n=2结果集为：
Begin End Time
111 222 2002-10-10
111 333 2002-10-11
111 222 2002-10-13
111 222 2002-10-14
111 333 2002-10-15
444 222 2002-10-19
444 222 2002-10-20
n=3结果集为：
Begin End Time
111 222 2002-10-10
111 222 2002-10-13
111 222 2002-10-14
不知这种情况的SQL语句如何写？
---------------------------------------------------------------
USE Northwind
GO
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id=object_id( N'[Flight]' ) AND OBJECTPROPERTY( id, N'IsUserTable' )=1 )
DROP TABLE [Flight]
GO
CREATE TABLE [Flight]
(
[Begin] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[End] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Time] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '111', '222', '2002-10-10' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '111', '333', '2002-10-11' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '111', '444', '2002-10-12' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '111', '222', '2002-10-13' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '111', '222', '2002-10-14' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '111', '333', '2002-10-15' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '222', '111', '2002-10-16' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '222', '333', '2002-10-17' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '222', '555', '2002-10-18' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '444', '222', '2002-10-19' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '444', '222', '2002-10-20' )
INSERT INTO [Flight]( [Begin], [End], [Time] ) VALUES( '666', '222', '2002-10-21' )
GO
SELECT * FROM [Flight]
GO
-- 方法一
DECLARE @n int -- 出现次数
SET @n=2
SELECT *
FROM [Flight] T1
WHERE ( SELECT COUNT(*)
FROM [Flight] T2
WHERE T2.[Begin]=T1.[Begin] AND T2.[End]=T1.[End] )>=@n
ORDER BY T1.[Begin], T1.[End], T1.[Time]
-- 方法二
DECLARE @n int -- 出现次数
SET @n=2
SELECT *
FROM [Flight] T1
WHERE EXISTS ( SELECT 1
FROM ( SELECT [Begin], [End]
FROM [Flight]
GROUP BY [Begin], [End]
HAVING COUNT(*)>=@n ) T2
WHERE T2.[Begin]=T1.[Begin] AND T2.[End]=T1.[End] )
ORDER BY T1.[Begin], T1.[End], T1.[Time]
posted on 2007-08-24 05:20
NeedJava 阅读(1384)
评论(0) 编辑 收藏 所属分类:
SQL