Ò»¸ö·³È˵ÄsqlÌâÄ¿ - MS-SQL Server / ÒÉÄÑÎÊÌâ

t_hykbgjl ¼Ç¼ÁË»áÔ±¿¨Ã¿´ÎµÄ·¢Éú¶î(nFse) ¡¢Óà¶î£¨nYe£©¼°¿¨ºÅ£¨sKH£©,¶ÔÓÚͬһ»áÔ±¿¨£¬ÉÏÒ»Ìõ¼Ç¼µÄÓà¶î¼ÓÉϱ¾´Î·¢Éú¶îÓ¦µÈÓÚ±¾´ÎµÄÓà¶î¡£·ñÔòÕʽ«²»Æ½¡£¼Ç¼ºÅΪsJlbh,¶ÔÓÚͬһ»áÔ±£¬ÏÂÒ»¼Ç¼ºÅ±ÈÉÏÒ»¼Ç¼ºÅ´ó1,Çëд³öÁгöËùÓлáÔ±¿¨²»Æ½ÕʼǼµÄSqlÓï¾ä¡£

Çó½â

---

Â¥Ö÷À´µã²âÊÔÊý¾Ý ¿´¿´

---

select a.* 
from t_hykbgjl a left join t_hykbgjl b
on a.sJlbh=b.sJlbh+1 
where b.nYe+a.nFse<>a.nYe

---

ÒýÓÃ

t_hykbgjl ¼Ç¼ÁË»áÔ±¿¨Ã¿´ÎµÄ·¢Éú¶î(nFse) ¡¢Óà¶î£¨nYe£©¼°¿¨ºÅ£¨sKH£©,¶ÔÓÚͬһ»áÔ±¿¨£¬ÉÏÒ»Ìõ¼Ç¼µÄÓà¶î¼ÓÉϱ¾´Î·¢Éú¶îÓ¦µÈÓÚ±¾´ÎµÄÓà¶î¡£·ñÔòÕʽ«²»Æ½¡£¼Ç¼ºÅΪsJlbh,¶ÔÓÚͬһ»áÔ±£¬ÏÂÒ»¼Ç¼ºÅ±ÈÉÏÒ»¼Ç¼ºÅ´ó1,Çëд³öÁгöËùÓлáÔ±¿¨²»Æ½ÕʼǼµÄSqlÓï¾ä¡£

Çó½â

--sql 2000ÓÃ×Ó²éѯ
SQL code:
select m.* , n.* from
(
select t.* , px = (select count(1) from t_hykbgjl where sKH = t.sKH and ¼Ç¼ºÅ < t.¼Ç¼ºÅ) + 1 from t_hykbgjl t
) m,
(
select t.* , px = (select count(1) from t_hykbgjl where sKH = t.sKH and ¼Ç¼ºÅ < t.¼Ç¼ºÅ) + 1 from t_hykbgjl t
) n
where m.sKH = n.sKH and n.px = m.px - 1 and m.nYe + n.nFse <> m.nYe



--sql 2005ÓÃrow_number
SQL code:
select m.* , n.* from
(
select t.* , px = row_number() over(