一、问题:
如果想对纪录中的几个字段相等的纪录查询出来,例如:在person表有如下数据:
现在我想查询出姓名、性别、年龄都相等的纪录,即期望的结果如下:
附录创建表person的语句:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[person]
GO
CREATE TABLE [dbo].[person] (
[id] [int] NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[age] [int] NULL
) ON [PRIMARY]
GO
二、解决问题
原来数据:
执行:
select * from person t where exists(select 1 from person where name=t.name and sex=t.sex and age=t.age and id <>t.id)
出现:
三、问题扩展
1、想要出现(三个字段重复的显示一个):
执行:
select * from person t where not exists(select 1 from person where name=t.name and sex=t.sex and age=t.age and id <t.id)
2、想要出现(三个字段重复的都不显示):
执行:
select * from person t where not exists(select 1 from person where name=t.name and sex=t.sex and age=t.age and id <>t.id)
3、想要出现(显示三个重复的字段,和重复字段的纪录的个数):
执行:
select count(*) total,name,sex,age from person group by name,sex,age having count(*) >1