列出一个表中的某个字段重复的记录

在某些时候,需要列出一个表中的某个字段重复的记录,如下:

================表结构=====================

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_Message]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t_Message]
GO

CREATE TABLE [dbo].[t_Message] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [Title] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
 [Content] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 [User_Id] [int] NULL ,
 [Input_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

================SQL语句====================

SELECT * FROM t_Message tm INNER JOIN (SELECT * FROM (SELECT Title,count(User_Id) AS Counts FROM t_Message GROUP BY Title) groupby WHERE groupby.Counts > 1) repeatfieid ON tm.Title = repeatfieid.Title

posted on 2006-05-01 01:33  老弹  阅读(...)  评论(...编辑  收藏

导航