SELECT * FROM
(SELECT n.id,n.l_typeID,c.Name,n.UpdateTime
FROM @news n
INNER JOIN @city c
ON CHARINDEX(','+RTRIM(c.id)+',',','+n.s_Reside+',')>0
) x
WHERE 3>(SELECT COUNT(1) FROM
(SELECT n.id,n.l_typeID,c.Name,n.UpdateTime
FROM @news n
INNER JOIN @city c
ON CHARINDEX(','+RTRIM(c.id)+',',','+n.s_Reside+',')>0
) y
WHERE y.Name=x.Name AND y.UpdateTime>x.UpdateTime)
DECLARE @news TABLE(id INT IDENTITY(1,1),l_TypeID INT,s_Reside VARCHAR(20))
INSERT @news
SELECT 1,'1,2,3,4'
UNION ALL SELECT 2,'2,3'
UNION ALL SELECT 1,'3,4'
UNION ALL SELECT 3,'1,2,4'
UNION ALL SELECT 5,'2,3,4,1'
DECLARE @city TABLE(id INT IDENTITY(1,1),Name VARCHAR(10))
INSERT @city SELECT 'aaa'
UNION ALL SELECT 'bbb'
UNION ALL SELECT 'ccc'
UNION ALL SELECT 'ddd'
SELECT * FROM
(SELECT n.id,n.l_typeID,c.Name
FROM @news n
INNER JOIN @city c
ON CHARINDEX(','+RTRIM(c.id)+',',','+n.s_Reside+',')>0
) x
WHERE 3>(SELECT COUNT(1) FROM
(SELECT n.id,n.l_typeID,c.Name
FROM @news n
INNER JOIN @city c
ON CHARINDEX(','+RTRIM(c.id)+',',','+n.s_Reside+',')>0
) y
WHERE y.Name=x.Name AND y.id>x.id)