SQL 想要对比当前行和上一行是否相同

[xiaxue77701]

我有这样的一个表test

prd_no rank
032030200154 A
032030200154 B
032020700026 D
032020700026 D
032030200154 C
033010100232 E

如果prd_no相同，rank不同时，在新一列显示rank用分号连接，希望得到的结果如下

prd_no rank jieguo
032030200154 A A;B;C
032030200154 B A;B;C
032020700026 D D
032020700026 D D
032030200154 C A;B;C
033010100232 E E

有大神知道怎么弄吗？

[二月十六]

语句：

--测试数据
;WITH test(prd_no,[rank])AS(
select '032030200154','A'  union all 
select '032030200154','B'  union all 
select '032020700026','D'  union all 
select '032020700026','D'  union all 
select '032030200154','C'  union all 
select '033010100232','E'  
)
--测试数据结束
,temp AS (
 SELECT  prd_no ,
            STUFF(( SELECT  ',' + test.[rank]
                    FROM    test
                    WHERE   prd_no = a.prd_no 
					GROUP BY rank
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS jieguo
    FROM    test a
    GROUP BY a.prd_no 
)
SELECT  test.* ,
        temp.jieguo
FROM    test
        INNER JOIN temp ON temp.prd_no = test.prd_no

结果：

[moudy]

WITH A (prd_no,rank) AS (SELECT '032030200154','A' UNION ALL SELECT '032030200154','B' UNION ALL SELECT '032020700026','D' UNION ALL SELECT '032020700026','D' UNION ALL SELECT '032030200154','C' UNION ALL SELECT '033010100232','E' ) --测试数据结束 SELECT t1.prd_no,t1.rank,STUFF(( SELECT DISTINCT ',' + t2.rank FROM A t2 WHERE t1.prd_no = t2.prd_no FOR XML PATH('') ), 1, 1, '') FROM A t1

[gw6328]

--测试数据
;WITH test(prd_no,[rank])AS(
select '032030200154','A'  union all 
select '032030200154','B'  union all 
select '032020700026','D'  union all 
select '032020700026','D'  union all 
select '032030200154','C'  union all 
select '033010100232','E'  
)
,c AS (
SELECT rn=ROW_NUMBER() OVER(ORDER BY GETDATE()) ,* FROM test -- 正常情况应该有个排序列，这里是我加上去
)
SELECT iif(a.prd_no=b.prd_no AND a.RANK=b.RANK,'与上一行相同','与上一行不同'),* FROM c a JOIN c b ON a.rn=b.rn+1

[LongRui888]

引用 1 楼 sinat_28984567 的回复:

语句：

--测试数据
;WITH test(prd_no,[rank])AS(
select '032030200154','A'  union all 
select '032030200154','B'  union all 
select '032020700026','D'  union all 
select '032020700026','D'  union all 
select '032030200154','C'  union all 
select '033010100232','E'  
)
--测试数据结束
,temp AS (
 SELECT  prd_no ,
            STUFF(( SELECT  ',' + test.[rank]
                    FROM    test
                    WHERE   prd_no = a.prd_no 
					GROUP BY rank
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS jieguo
    FROM    test a
    GROUP BY a.prd_no 
)
SELECT  test.* ,
        temp.jieguo
FROM    test
        INNER JOIN temp ON temp.prd_no = test.prd_no

结果：

[二月十六]

引用 4 楼 yupeigu 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 语句：

--测试数据
;WITH test(prd_no,[rank])AS(
select '032030200154','A'  union all 
select '032030200154','B'  union all 
select '032020700026','D'  union all 
select '032020700026','D'  union all 
select '032030200154','C'  union all 
select '033010100232','E'  
)
--测试数据结束
,temp AS (
 SELECT  prd_no ,
            STUFF(( SELECT  ',' + test.[rank]
                    FROM    test
                    WHERE   prd_no = a.prd_no 
					GROUP BY rank
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS jieguo
    FROM    test a
    GROUP BY a.prd_no 
)
SELECT  test.* ,
        temp.jieguo
FROM    test
        INNER JOIN temp ON temp.prd_no = test.prd_no

结果：

[/quote]