select left(姓名,1),count(*) from tb group by left(姓名,1) having count(*)>=2
left(name,1)相等--单姓,李、 union left(name,2)相等--双姓,欧阳、
select * form t1 where name like '姓%'
wen 问到重点了。 。强
create table #CCC ( id int identity(1,1) primary key not null, CName nvarchar(20) ) insert into #CCC select '胡1', insert into #CCC select '胡2' insert into #CCC select '2胡' insert into #CCC select '刘胡兰' insert into #CCC select '刘三姐' insert into #CCC select '王五' insert into #CCC select '李四' insert into #CCC select '张三' insert into #CCC select '张三丰' insert into #CCC select '张无忌'select CName from #CCC C where count(CName)>2 select CName from #CCC C where exists (select * from #CCC where left(CName,1)=left(C.CName,1) or left(CName,2)=left(C.CName,2) having count(CName)>=2)
union
left(name,2)相等--双姓,欧阳、
(
id int identity(1,1) primary key not null,
CName nvarchar(20)
)
insert into #CCC select '胡1',
insert into #CCC select '胡2'
insert into #CCC select '2胡'
insert into #CCC select '刘胡兰'
insert into #CCC select '刘三姐'
insert into #CCC select '王五'
insert into #CCC select '李四'
insert into #CCC select '张三'
insert into #CCC select '张三丰'
insert into #CCC select '张无忌'select CName from #CCC C where count(CName)>2 select CName from #CCC C where exists (select * from #CCC where left(CName,1)=left(C.CName,1) or left(CName,2)=left(C.CName,2) having count(CName)>=2)