posts - 41,  comments - 40,  trackbacks - 0
要查询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 阅读(1267) 评论(0)  编辑  收藏 所属分类: SQL

只有注册用户登录后才能发表评论。


网站导航: