如何删除一个字段里重复的数并按照从小到大的顺序排列
例如在表tb1里有17778行数据，数据在字段notext里，TTD字段代表数据的ID,请问如何用sql语句完成
TDD NOTEXT
1 13 07 04 00 12 10 12 03 02 01 13 13 10 09 15
2 09 04 13 14 11 10 12 01 05 05 05 05 08 07 16
3 11 07 07 12 10 10 12 02 04 06 13 13 04 13 03
4 10 07 02 14 13 14 00 00 12 02 11 11 07 06 07
5 06 04 10 13 13 15 01 04 01 02 04 04 03 12 03
6 05 04 11 08 09 12 14 11 01 02 16 16 09 08 10
7 08 08 05 12 14 02 04 00 03 08 10 10 08 07 11
8 07 08 01 13 00 05 07 15 06 03 01 01 05 04 12
9 02 04 05 01 05 11 13 01 08 04 11 11 02 11 13
10 04 07 03 11 00 07 09 01 07 01 08 02 06 05 16
11 01 05 05 11 01 09 11 07 02 01 12 06 04 13 02
12 06 11 01 10 01 10 12 01 03 10 02 12 02 11 16
13 00 06 04 08 00 10 12 08 09 05 04 14 04 13 16
14 00 07 05 04 13 08 10 01 10 02 11 05 03 12 06
15 06 14 06 04 14 10 12 10 06 07 08 02 04 13 16
16 15 08 08 08 03 00 02 00 02 06 02 12 05 14 10
一、出现00就删除掉；
二、删除重复的数值；
三、按照从小到大的顺序排列在字段notext 里？谢谢！
删除重复，排序
[解决办法]
create table #tb(notext varchar(100))
insert into #tb
select'13 07 04 00 12 10 12 03 02 01 13 13 10 09 15'
union all select'09 04 13 14 11 10 12 01 05 05 05 05 08 07 16'
union all select'11 07 07 12 10 10 12 02 04 06 13 13 04 13 03'
union all select'10 07 02 14 13 14 00 00 12 02 11 11 07 06 07'
union all select'06 04 10 13 13 15 01 04 01 02 04 04 03 12 03'
union all select'05 04 11 08 09 12 14 11 01 02 16 16 09 08 10'
union all select'08 08 05 12 14 02 04 00 03 08 10 10 08 07 11'
union all select'07 08 01 13 00 05 07 15 06 03 01 01 05 04 12'
union all select'02 04 05 01 05 11 13 01 08 04 11 11 02 11 13'
union all select'04 07 03 11 00 07 09 01 07 01 08 02 06 05 16'
union all select'01 05 05 11 01 09 11 07 02 01 12 06 04 13 02'
union all select'06 11 01 10 01 10 12 01 03 10 02 12 02 11 16'
union all select'00 06 04 08 00 10 12 08 09 05 04 14 04 13 16'
union all select'00 07 05 04 13 08 10 01 10 02 11 05 03 12 06'
union all select'06 14 06 04 14 10 12 10 06 07 08 02 04 13 16'
union all select'15 08 08 08 03 00 02 00 02 06 02 12 05 14 10'
go
create function dbo.fn_text (@notext varchar(100))
returns varchar(100)
as
begin
declare @t table(col char(2))
declare @s varchar(100),@v varchar(2)
set @s=@notext
while len(@s)>0
begin
set @v=left(@s,2)
if @v<>'00'
insert into @t(col) values(left(@s,2))
set @s=stuff(@s,1,3,'')
end
set @s=''
select @s=@s+' '+col from (select distinct col from @t)t order by col
set @s=stuff(@s,1,1,'')
return (@s)
end
go
select dbo.fn_text(notext) as result from #tb
drop function dbo.fn_text
drop table #tb
/*
01 02 03 04 07 09 10 12 13 15
01 04 05 07 08 09 10 11 12 13 14 16
02 03 04 06 07 10 11 12 13
02 06 07 10 11 12 13 14
01 02 03 04 06 10 12 13 15
01 02 04 05 08 09 10 11 12 14 16
02 03 04 05 07 08 10 11 12 14
01 03 04 05 06 07 08 12 13 15
01 02 04 05 08 11 13
01 02 03 04 05 06 07 08 09 11 16
01 02 04 05 06 07 09 11 12 13
01 02 03 06 10 11 12 16
04 05 06 08 09 10 12 13 14 16
01 02 03 04 05 06 07 08 10 11 12 13
02 04 06 07 08 10 12 13 14 16
02 03 05 06 08 10 12 14 15
*/
--临时表#tb没有ID列, 只有TDD表,应该这样
UPDATE a
SET MMD = b.result
FROM [dbo].[sqqnotext] a
INNER JOIN (select TDD,dbo.fn_text(notext) as result from #tb) b
on a.TDD=b.TDD
