难：如何获取列内容中最大值，不是max()这么简单
如下：
比如有两行数据，有可能N行，N为1到无限：
表a中有列t:
内容为：
第一行t数据：671:233,789:136,736:202,539:244,505:88,504:186,500:190,332:217,204:211,205:93,362:184,410:246,615:372,685:317,685:317,429:399,429:401,476:492,991:364,1026:249,1025:166,949:150,733:71,175:65,170:69,175:173,475:309,616:309,810:324,813:324,767:226,763:175,770:108,770:101,735:74,470:106,471:176,484:213,489:222,598:292,1140:249,1187:194,1057:344,977:256,959:262,536:171,533:115,576:186,647:233,647:233
第二行t数据：
754:73,734:193,718:229,622:304,608:302,891:303,860:218,832:184,803:129,745:52,752:215,711:357,964:497,470:512,264:410,192:279,192:46,286:179,598:215,577:121,681:219,782:302,778:313,700:330

所有行数据中以逗号“，”分割，冒号“:”右边的最大值是多少？


------解决方案--------------------

CREATE TABLE tb(id INT,col VARCHAR(max))

INSERT INTO tb

SELECT 

1,'671:233,789:136,736:202,539:244,505:88,504:186,500:190,332:217,204:211,205:93,362:184,410:246,615:372,685:317,685:317,429:399,429:401,476:492,991:364,1026:249,1025:166,949:150,733:71,175:65,170:69,175:173,475:309,616:309,810:324,813:324,767:226,763:175,770:108,770:101,735:74,470:106,471:176,484:213,489:222,598:292,1140:249,1187:194,1057:344,977:256,959:262,536:171,533:115,576:186,647:233,647:233'

UNION ALL

SELECT 

2,'754:73,734:193,718:229,622:304,608:302,891:303,860:218,832:184,803:129,745:52,752:215,711:357,964:497,470:512,264:410,192:279,192:46,286:179,598:215,577:121,681:219,782:302,778:313,700:330'





;WITH maco AS 

(

SELECT A.id, B.col,RIGHT(b.COL,LEN(B.COL)-CHARINDEX(':',B.COL)) AS rcol

FROM(

  SELECT id, col = CONVERT(xml,'<root><v>' + REPLACE(col, ',', '</v><v>') + '</v></root>') FROM tb

)A

OUTER APPLY(

  SELECT col = N.v.value('.', 'varchar(100)') FROM A.col.nodes('/root/v') N(v)

)B 

)



SELECT id,MAX(rcol+0) AS maxrco FROM maco GROUP BY id

/*

id          maxrco

----------- -----------

1           492

2           512

*/