tab1
rowId aa
1 20,20,20
2 34,34,34,29,29,29,29,24,24,24,24,19,19,19,19,15,15,15,11,11,11,7,7,7,5,3,2,1求返回结果
tab2
rowId aa
1 20
2 34,29,24,19,15,11,7,5,3,2,1
rowId aa
1 20,20,20
2 34,34,34,29,29,29,29,24,24,24,24,19,19,19,19,15,15,15,11,11,11,7,7,7,5,3,2,1求返回结果
tab2
rowId aa
1 20
2 34,29,24,19,15,11,7,5,3,2,1
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([rowId] [int],[aa] [nvarchar](80))
INSERT INTO [tb]
SELECT '1','20,20,20' UNION ALL
SELECT '2','34,34,34,29,29,29,29,24,24,24,24,19,19,19,19,15,15,15,11,11,11,7,7,7,5,3,2,1'--SELECT * FROM [tb]-->SQL查询如下:
;WITH t AS
(
SELECT a.[rowId], b.[aa] ,
rn=ROW_NUMBER()OVER(PARTITION BY a.[rowId] ORDER BY CAST(b.[aa] AS FLOAT) DESC)
FROM (
SELECT [rowId], [aa] = CONVERT(XML, '<v>'+REPLACE([aa], ',', '</v><v>')+'</v>')
FROM tb
) a
CROSS APPLY(
SELECT aa = n.v.value('.', 'varchar(10)')
FROM a.[aa].nodes('/v') n(v)
) b
GROUP BY a.[rowId], b.[aa]
)
SELECT DISTINCT rowID, aa = STUFF(
(
SELECT ','+aa
FROM t
WHERE rowID = a.rowID
ORDER BY rn
FOR XML PATH('')
), 1, 1, ''
)
FROM t a
/*
rowID aa
----------- -------------------------------------------
1 20
2 34,29,24,19,15,11,7,5,3,2,1(2 行受影响)
*/
WITH TT2
AS(
SELECT TOP 100 percent rowid,aa
FROM (
select
a.rowid,aa = CONVERT(INT,b.aa)
from
(select rowid,aa=convert(xml,'<root><v>'+replace(aa,',','</v><v>')+'</v></root>') from Tab1)a
outer apply
(select aa=C.v.value('.','nvarchar(100)') from a.aa.nodes('/root/v')C(v))b)TT
GROUP BY rowid,aa
ORDER BY rowid,aa desc
)
--合并
select rowid, [aa]=stuff((select ','+CONVERT(VARCHAR(10),aa) from TT2 t where rowid=TT2.rowid ORDER BY aa DESC for xml path('')), 1, 1, '' )
from TT2
group by rowid
/*
rowid aa
1 20
2 34,29,24,19,15,11,7,5,3,2,1*/
--trydeclare @tb table (rowid int,aa nvarchar(1000))
insert into @tb select 1,'20,20,20'
union all select 2,'34,34,34,29,29,29,29,24,24,24,24,19,19,19,19,15,15,15,11,11,11,7,7,7,5,3,2,1'
;With China
as
(
select a.rowid,b.value from
(select rowid,aa=convert(xml,'<root><v>'+replace([aa],',','</v><v>')+'</v></root>') from @tb)
A
outer apply
(
select [value]=N.v.value('.','varchar(1000)') from A.aa.nodes('/root/v') N(v)
) B
)
,China1 as
(
select * from China group by rowid,value
)
select rowid,[value]=stuff(
(select ','+[value] from china1 where a.rowid=rowid for xml path('')),1,1,'') from china1 a
group by rowid
/*rowid value
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 20
2 1,11,15,19,2,24,29,3,34,5,7(2 行受影响)
*/