如何删除字段里重复的数据(但保留该重复数值),并且按照从小到大的顺序排列在一个字段，还要统计重复数值的个数
如何删除字段里重复的数据(但保留该重复数值),并且按照从小到大的顺序排列在一个字段，还要统计重复数值的个数
例如在表tb1里有17778行数据，数据在字段 [notext] 里，TTD字段代表数据的ID,请问如何用sql语句完成以下要求：
select '04 13 11 14 09 06 08 13 02 01 13 13 10 09 15'
union all select '13 07 04 00 12 10 12 03 05 05 05 05 08 07 16'
union all select '09 04 13 14 11 10 12 01 04 06 13 13 04 13 03'
union all select '11 07 07 12 10 10 12 02 12 02 11 11 07 06 07'
union all select '10 07 02 14 13 14 00 00 01 02 04 04 03 12 03'
union all select '06 04 10 13 13 15 01 04 01 02 16 16 09 08 10'
union all select '05 04 11 08 09 12 14 11 03 08 10 10 08 07 11'
union all select '08 08 05 12 14 02 04 00 06 03 01 01 05 04 12'
union all select '07 08 01 13 00 05 07 15 08 04 11 11 02 11 13'
union all select '02 04 05 01 05 11 13 01 07 01 08 02 06 05 16'
现在需要完成
1、请按照TDD排序（如下图顺序很重要）；
2、出现数值 00 就删除掉；
3、删除重复的数据（但保留该重复数值），并且按照从小到大的顺序排列在一个字段 [MMD]里；
4、把重复的数据统计出来，按照重复的次数分别写在字段[RT2],[RT3],[RT4],[RT5],[RT6],[RT7]里，查询结果如下图所示
----[RT2]代表重复2次的数据，[RT3]代表重复3次的数据，以此类推。
可参考http://bbs.csdn.net/topics/390625528?page=1#post-395896477 重复数据删除，数据合并，统计
[解决办法]
create table #tb(id int identity(1,1),notext varchar(100))
insert into #tb(notext)
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
create function dbo.fn_text2 (@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+'/'+cast(num as varchar)+';' from (select col,count(*) as num from @t group by col having count(*)>1)t order by col
set @s=stuff(@s,1,1,'')
return (@s)
end
go
select id ,notext,dbo.fn_text(notext) as MMD,dbo.fn_text2(notext) as TR from #tb
drop function dbo.fn_text
drop function dbo.fn_text2
drop table #tb
/*
id notext MMD TR
-----------------------------------------------------------------------------------------------------------------
113 07 04 00 12 10 12 03 02 01 13 13 10 09 1501 02 03 04 07 09 10 12 13 1510/2; 12/2; 13/3;
209 04 13 14 11 10 12 01 05 05 05 05 08 07 1601 04 05 07 08 09 10 11 12 13 14 1605/4;
311 07 07 12 10 10 12 02 04 06 13 13 04 13 0302 03 04 06 07 10 11 12 1304/2; 07/2; 10/2; 12/2; 13/3;
410 07 02 14 13 14 00 00 12 02 11 11 07 06 0702 06 07 10 11 12 13 1402/2; 07/3; 11/2; 14/2;
506 04 10 13 13 15 01 04 01 02 04 04 03 12 0301 02 03 04 06 10 12 13 1501/2; 03/2; 04/4; 13/2;
605 04 11 08 09 12 14 11 01 02 16 16 09 08 1001 02 04 05 08 09 10 11 12 14 1608/2; 09/2; 11/2; 16/2;
708 08 05 12 14 02 04 00 03 08 10 10 08 07 1102 03 04 05 07 08 10 11 12 1408/4; 10/2;
807 08 01 13 00 05 07 15 06 03 01 01 05 04 1201 03 04 05 06 07 08 12 13 1501/3; 05/2; 07/2;
902 04 05 01 05 11 13 01 08 04 11 11 02 11 1301 02 04 05 08 11 1301/2; 02/2; 04/2; 05/2; 11/4; 13/2;
1004 07 03 11 00 07 09 01 07 01 08 02 06 05 1601 02 03 04 05 06 07 08 09 11 1601/2; 07/3;
1101 05 05 11 01 09 11 07 02 01 12 06 04 13 0201 02 04 05 06 07 09 11 12 1301/3; 02/2; 05/2; 11/2;
1206 11 01 10 01 10 12 01 03 10 02 12 02 11 1601 02 03 06 10 11 12 1601/3; 02/2; 10/3; 11/2; 12/2;
1300 06 04 08 00 10 12 08 09 05 04 14 04 13 1604 05 06 08 09 10 12 13 14 1604/3; 08/2;
1400 07 05 04 13 08 10 01 10 02 11 05 03 12 0601 02 03 04 05 06 07 08 10 11 12 1305/2; 10/2;
1506 14 06 04 14 10 12 10 06 07 08 02 04 13 1602 04 06 07 08 10 12 13 14 1604/2; 06/3; 10/2; 14/2;
1615 08 08 08 03 00 02 00 02 06 02 12 05 14 1002 03 05 06 08 10 12 14 1502/3; 08/3;
*/
IF OBJECT_ID('tempdb..#TempA','U') IS NOT NULL DROP TABLE #TempA
CREATE TABLE #TempA(Id INT IDENTITY(1,1),Notext VARCHAR(100))
INSERT INTO #TempA(Notext)
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'
IF OBJECT_ID('tempdb..#TempB','U') IS NOT NULL DROP TABLE #TempB
CREATE TABLE #TempB
(
Rn INT IDENTITY(1,1)
,IDINT
,NumCHAR(2)
)
DECLARE @Cnt INT=(SELECT MAX(Id) FROM #TempA)
DECLARE @Txt VARCHAR(200)='',@Num VARCHAR(2)=''
WHILE @Cnt>0
BEGIN
SELECT @Txt=Notext FROM #TempA WHERE Id=@Cnt
WHILE LEN(@Txt)>0
BEGIN
SET @Num=left(@Txt,2)
IF @Num<>'00'
INSERT INTO #TempB(ID,Num) VALUES(@Cnt,left(@Txt,2))
SET @Txt=STUFF(@Txt,1,3,'')
END
SET @Cnt=@Cnt-1
END
IF OBJECT_ID('tempdb..#TempC','U') IS NOT NULL DROP TABLE #TempC
CREATE TABLE #TempC
(
ID INT
,Num CHAR(2)
,Cnt INT
)
INSERT INTO #TempC
SELECT A.ID
,A.Num
,SUM(CASE WHEN A.Num=T.Num THEN 1 ELSE 0 END ) AS Cnt
FROM #TempB AS A
CROSS APPLY (
SELECT B.Num FROM #TempB AS B WHERE A.ID=B.ID AND A.Rn=B.Rn
) AS T
GROUP BY A.ID,A.Num
ORDER BY ID
SELECT A.ID
,B.Notext
,B1.Num
,ISNULL(B1.RT2,'') AS [RT2]
,ISNULL(B1.RT3,'') AS [RT3]
,ISNULL(B1.RT4,'') AS [RT4]
,ISNULL(B1.RT5,'') AS [RT5]
,ISNULL(B1.RT6,'') AS [RT6]
,ISNULL(B1.RT7,'') AS [RT7]
FROM #TempC AS A
JOIN #TempA AS B ON A.ID=B.Id
OUTER APPLY (
SELECT Num=STUFF((SELECT ' '+B.Num FROM #TempC AS B WHERE A.ID=B.ID FOR XML PATH('')),1,1,'')
,[RT2]=STUFF((SELECT ' '+C.Num FROM #TempC AS C WHERE A.ID=C.ID AND C.Cnt=2 FOR XML PATH('')),1,1,'')
,[RT3]=STUFF((SELECT ' '+D.Num FROM #TempC AS D WHERE A.ID=D.ID AND D.Cnt=3 FOR XML PATH('')),1,1,'')
,[RT4]=STUFF((SELECT ' '+E.Num FROM #TempC AS E WHERE A.ID=E.ID AND E.Cnt=4 FOR XML PATH('')),1,1,'')
,[RT5]=STUFF((SELECT ' '+F.Num FROM #TempC AS F WHERE A.ID=F.ID AND F.Cnt=5 FOR XML PATH('')),1,1,'')
,[RT6]=STUFF((SELECT ' '+G.Num FROM #TempC AS G WHERE A.ID=G.ID AND G.Cnt=6 FOR XML PATH('')),1,1,'')
,[RT7]=STUFF((SELECT ' '+H.Num FROM #TempC AS H WHERE A.ID=H.ID AND H.Cnt=7 FOR XML PATH('')),1,1,'')
) AS B1
GROUP BY A.ID,B.Notext,B1.Num,B1.RT2,B1.RT3,B1.RT4,B1.RT5,B1.RT6,B1.RT7
