34,576
社区成员
发帖
与我相关
我的任务
分享
select * from [user] where id in (select min(id) from [user] group by left(LoginName,3))
select * from [user] where id in (select min(id) from [user] group by left(LoginName,3))
--函数
create function f_compstr(
@str1 varchar(8000),
@str2 varchar(8000)
) returns bit
as
begin
declare @re bit,@lstr varchar(8000),@sstr varchar(8000)
declare @tb table(id int identity(1,1),a int)
insert into @tb(a) select top 100 null from sysobjects a,sysobjects b
if len(@str1)>len(@str2)
select @lstr=@str1,@sstr=@str2
else select @lstr=@str2,@sstr=@str1
set @re=case when exists(
select 1
from (select name=@sstr)a,@tb b
where (b.id<=len(@sstr)-2 and @lstr like '%'+substring(name,b.id,3)+'%')
or (b.id<=len(@sstr)-1 and @lstr like '%'+substring(name,b.id,2)+'%' and patindex('%[^吖-座]%',substring(name,b.id,2))=0)
) then 1 else 0 end
return(@re)
end
--测试数据
declare @t table(ID int identity(1,1),loginname varchar(20),password varchar(10))
insert @t select 'haha50005','123'
union all select 'haha50006' ,'465679'
union all select '50006haha' ,'469'
union all select 'の删除记忆…','456'
union all select '美好的记忆”','123456'
select * from @t a where id =(select min(id) from @t where dbo.f_compstr(a.loginname,loginname)=1)
--结果
/*
ID loginname password
----------- -------------------- ----------
1 haha50005 123
4 の删除记忆… 456
(所影响的行数为 2 行)
*/