select top 10 * from (select distinct * from [Table] ) aa where [Col] like '[a-zA-Z]%[0-9]' order by 排序字段
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-16 09:03:11 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/ --> 生成测试数据表:aIF OBJECT_ID('[a]') IS NOT NULL DROP TABLE [a] GO CREATE TABLE [a]([a1] NVARCHAR(30)) INSERT [a] SELECT '012233254654675365765' UNION ALL SELECT '035654756876887765765' UNION ALL SELECT '335654758' GO --SELECT * FROM [a]-->SQL查询如下: IF OBJECT_ID('[f_getnum]') IS NOT NULL DROP function [f_getnum] GO create function f_getnum(@s nvarchar(1000)) returns nvarchar(20) as begin declare @r nvarchar(20) set @r='' while len(@s)>0 begin if substring(@s,1,1)=substring(@s+'a',2,1) set @s=stuff(@s,1,1,'') else select @r=@r+left(@s,1),@s=stuff(@s,1,1,'') end return left(@r,10) end goselect dbo.f_getnum(a1) from a /* -------------------- 0123254654 0356547568 35654758(3 行受影响) */
where [Col] like '[a-zA-Z]%[0-9]'
select distinct left(a1,10) from a limit 10
where [Col] like '[a-zA-Z]%[0-9]'
order by 排序字段
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-16 09:03:11
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:aIF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([a1] NVARCHAR(30))
INSERT [a]
SELECT '012233254654675365765' UNION ALL
SELECT '035654756876887765765' UNION ALL
SELECT '335654758'
GO
--SELECT * FROM [a]-->SQL查询如下:
IF OBJECT_ID('[f_getnum]') IS NOT NULL
DROP function [f_getnum]
GO
create function f_getnum(@s nvarchar(1000))
returns nvarchar(20)
as
begin
declare @r nvarchar(20)
set @r=''
while len(@s)>0
begin
if substring(@s,1,1)=substring(@s+'a',2,1)
set @s=stuff(@s,1,1,'')
else
select @r=@r+left(@s,1),@s=stuff(@s,1,1,'')
end
return left(@r,10)
end
goselect dbo.f_getnum(a1) from a
/*
--------------------
0123254654
0356547568
35654758(3 行受影响)
*/
select substr(列,0,10)from 表