要查詢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 閱讀(1396)
評論(0) 編輯 收藏 所屬分類:
SQL