Sql Server使用cursor处理重复数据过程详解

时间:2022-08-29 14:07:26

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

/************************************************************

 * Code formatted by setyg

 * Time: 2014/7/29 10:04:44

 ************************************************************/

 

CREATE PROC HandleEmailRepeat

AS

DECLARE email CURSOR

FOR

  SELECT e.email

     ,e.OrderNo

     ,e.TrackingNo

  FROM  Email20140725 AS e

  WHERE e.[status] = 0

  ORDER BY

      e.email

     ,e.OrderNo

     ,e.TrackingNo

 

BEGIN

  DECLARE @@email       VARCHAR(200)

      ,@firstEmail     VARCHAR(200)

      ,@FirstOrderNO    VARCHAR(300)

      ,@FirstTrackingNO   VARCHAR(300)

      ,@NextEmail      VARCHAR(200)

      ,@@orderNO      VARCHAR(300)

      ,@NextOrderNO     VARCHAR(50)

      ,@@trackingNO     VARCHAR(300)

      ,@NextTrackingNO   VARCHAR(50)

   

  BEGIN

    OPEN email;

    FETCH NEXT FROM email INTO @firstEmail,@FirstOrderNO, @FirstTrackingNO;

    FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO, @NextTrackingNO;

    IF @NextEmail!=@firstEmail

    BEGIN

      INSERT INTO Email20140725Test

       (

        email

        ,OrderNo

        ,TrackingNo

       )

      VALUES

       (

        @firstEmail

        ,@FirstOrderNO

        ,@FirstTrackingNO

       ); 

      SET @@email = @NextEmail;

      SET @@orderNO = @NextOrderNO;

      SET @@trackingNO = @NextTrackingNO;

    END

    ELSE

    BEGIN

      SET @@email = @NextEmail;

      SET @@orderNO = @FirstOrderNO+'、'+@NextOrderNO;

      SET @@trackingNO = @FirstTrackingNO+'、'+@NextTrackingNO;

    END

     

     

     

    FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO,@NextTrackingNO

    WHILE @@fetch_status=0

    BEGIN

      IF @NextEmail=@@email

      BEGIN

        IF (@NextOrderNO!=@@orderNO)

          SET @@orderNO = @@orderNO+'、'+@NextOrderNO

         

        PRINT 'orderNO:'+@@orderNO 

         

        IF (@@trackingNO!=@NextTrackingNO)

          SET @@trackingNO = @@trackingNO+'、'+@NextTrackingNO

         

        PRINT 'trackingNO:'+@@trackingNO

      END

      ELSE

      BEGIN

        INSERT INTO Email20140725Test

         (

          email

          ,OrderNo

          ,TrackingNo

         )

        VALUES

         (

          @@email

          ,@@orderNO

          ,@@trackingNO

         );

        SET @@email = @NextEmail;

        SET @@orderNO = @NextOrderNO;

        SET @@trackingNO = @NextTrackingNO;

      END

      FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO, @NextTrackingNO;

    END

    CLOSE email; --关闭游标

    DEALLOCATE email; --释放游标

  END

END