日期:2014-05-17 浏览次数:20646 次
--1 ;WITH t AS ( SELECT fdSn,fd=left(fdSn,1),sn=CAST(STUFF(fdSn,1,1,'')AS INT)from Table1 ) select fdSn=fdSn+(SELECT ISNULL('-'+MIN(fdSn),'') FROM t AS b WHERE fd=a.fd AND sn>a.sn AND NOT EXISTS (SELECT 1 FROM t WHERE fd=b.fd AND sn=b.sn+1) AND EXISTS (SELECT 1 FROM t WHERE fd=b.fd AND sn=a.sn+1) ) from t AS a WHERE NOT EXISTS ( SELECT 1 FROM t WHERE fd=a.fd AND sn=a.sn-1) /* fdSn ----------------------------------------------------------------- A001-A003 A099-A100 A055 A058-A059 A012 (5 行受影响) */ ;WITH t AS ( SELECT fdSn,fd=left(fdSn,1),sn=CAST(STUFF(fdSn,1,1,'')AS INT)from Table1 ) select fdSn=fdSn+'-'+ISNULL((SELECT MIN(fdSn) FROM t AS b WHERE fd=a.fd AND sn>a.sn AND NOT EXISTS (SELECT 1 FROM t WHERE fd=b.fd AND sn=b.sn+1) AND EXISTS (SELECT 1 FROM t WHERE fd=b.fd AND sn=a.sn+1) ),fdSn) from t AS a WHERE NOT EXISTS ( SELECT 1 FROM t WHERE fd=a.fd AND sn=a.sn-1) /* fdSn ----------------------------------------------------------------- A001-A003 A099-A100 A055-A055 A058-A059 A012-A012 (5 行受影响) */