none
查询数据后怎么自动添加多一例来说明查询出来的相同数据 RRS feed

  • 问题

  • 如:Indate                         name                       count
         2011\05\01                     A                              1
         2011\05\01                     B                              2
         2011\05\02                     A                              1
         2011\05\02                     B                              2
         2011\05\02                     C                              3
         2011\05\03                     A                              1
         2011\05\03                     B                              2
    如上数据显示:Indate .name两例是查询出来的数据,count例是查询出来后自动添加的例,主要来标识前面有相同日期的
    问题:怎么自动添加例,怎么样对前面相同的日期进行像以上数据一样排例标识

    2011年6月15日 1:03

答案

  • 你好 tankin,

     

    关于您的这个问题,我们只需row_number()这个函数就可以实现,测试代码如下:

    CREATE TABLE INDATE
     (INDATE NVARCHAR(20),
     NAME NVARCHAR(20))
    GO
    INSERT INTO INDATE
    VALUES ('2011\05\01','A')
    INSERT INTO INDATE
    VALUES ('2011\05\01','B')
    INSERT INTO INDATE
    VALUES ('2011\05\02','A')
    INSERT INTO INDATE
    VALUES ('2011\05\02','B')
    INSERT INTO INDATE
    VALUES ('2011\05\02','C')
    INSERT INTO INDATE
    VALUES ('2011\05\03','A')
    INSERT INTO INDATE
    VALUES ('2011\05\03','B')
    GO
    SELECT INDATE,NAME,ROW_NUMBER() OVER(PARTITION BY INDATE ORDER BY INDATE,NAME)AS COUNT1
    FROM INDATE
    GO
    

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    2011年6月21日 7:29
    版主