MSSQL求助..一个去重复问题

[lianzuo123]

select entryId, billNo,AddTime,consignorCname,cusDecStatus,updateTime , cusDecStatusName = (STUFF((select ',' + cusDecStatusName from cusDecStatusData where entryId = a.entryId for xml path('')),1,1,'')) from cusDecStatusData a where billNo LIKE 'EGLV0508%' group by consignorCname,entryId,billNo,AddTime,cusDecStatus,updateTime ORDER BY AddTime DESC

这个是现在的语句



上图是查询结果..

如何去除重复的EntryId的那条记录,只显示AddTime最晚的那条.即上图里上面那条

[二月十六]

SELECT
    *
FROM
    (
        SELECT
            entryId,
            billNo,
            AddTime,
            consignorCname,
            cusDecStatus,
            updateTime,
            cusDecStatusName    = (STUFF(
                                       (
                                           SELECT
                                               ',' + cusDecStatusName
                                           FROM
                                               cusDecStatusData
                                           WHERE
                                               entryId = a.entryId
                                           FOR XML PATH('')
                                       ), 1, 1, ''
                                        )
                                  ),
            ROW_NUMBER() OVER (PARTITION BY
                                   EntryId
                               ORDER BY
                                   AddTime DESC
                              ) rn
        FROM
            cusDecStatusData a
        WHERE
            billNo LIKE 'EGLV0508%'
        GROUP BY
            consignorCname,
            entryId,
            billNo,
            AddTime,
            cusDecStatus,
            updateTime
    ) t
WHERE
    rn = 1
ORDER BY
    AddTime DESC;

[吉普赛的歌]

SELECT *
FROM (
	SELECT 
		   ROW_NUMBER() OVER (PARTITION BY billNo ORDER BY addTime DESC) AS rid	
		   entryId,
		   billNo,
		   AddTime,
		   consignorCname,
		   cusDecStatus,
		   updateTime,
		   cusDecStatusName = (
			   STUFF(
				   (
					   SELECT ',' + cusDecStatusName
					   FROM   cusDecStatusData
					   WHERE  entryId = a.entryId FOR XML PATH('')
				   ),
				   1,
				   1,
				   ''
			   )
		   )
	FROM   cusDecStatusData a
	WHERE  billNo LIKE 'EGLV0508%'
	GROUP BY
		   consignorCname,
		   entryId,
		   billNo,
		   AddTime,
		   cusDecStatus,
		   updateTime
) AS t
WHERE rid=1
ORDER BY
       AddTime DESC

[吉普赛的歌]

错了， 是：

PARTITION BY entryId ORDER BY addTime DESC

[doloopcn]

select entryId, billNo,AddTime,consignorCname,cusDecStatus,updateTime ,
cusDecStatusName = (STUFF((
select ',' + cusDecStatusName
from cusDecStatusData
where entryId = a.entryId for xml path('')),1,1,''))
from
//使用最简单的MAX查询
//***************************************************************************************************//
(SELECT entryId, billNo,AddTime,consignorCname,cusDecStatus,MAX(updateTime) updateTime
where billNo LIKE 'EGLV0508%'
group by consignorCname,entryId,billNo,AddTime,cusDecStatus) a
//***************************************************************************************************//
group by consignorCname,entryId,billNo,AddTime,cusDecStatus,updateTime
ORDER BY AddTime DESC