dongxi0523
2009-10-11 12:45
浏览 40

通过查询或脚本在mysql表中插入rownum

I have a table that has 5 fields and has a total of 400 records. I added a new field which I called 'order' which, as the name suggests, is to keep an order in the records.

Since I added this after the records had been inserted I was wondering if there was a way to populate this field with the current order of the records using something like rownum via a sql query or do I need to use a PHP script to do the insert?

PS: I tried googling for rownum and didn't find anything useful :(

图片转代码服务由CSDN问答提供 功能建议

我有一个包含5个字段的表,总共有400条记录。 我添加了一个新的字段,我称之为“订单”,顾名思义,它是在记录中保留一个订单。

由于我在插入记录后添加了这个,我是 想知道是否有办法使用rownum通过SQL查询来记录当前的记录顺序,或者我是否需要使用PHP脚本来执行插入?

PS:我尝试用谷歌搜索rownum并没有找到任何有用的东西:(

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douchungu0859 2009-10-11 12:54
    已采纳

    I don't think it is possible in SQL but you can use your primary key as initial order. After all, by default rows are sorted based on it anyway.

    UPDATE table SET order = id;
    
    点赞 评论
  • dongqiang2024 2009-10-11 12:53

    ROWNUM is Oracle specific syntax. MySQL doesn't have a ranking function - the closest you can get is to use:

    SELECT t.*, 
           @rownum := @rownum+1 'rownum'
      FROM TABLE t, (SELECT @rownum := 0) r
    

    ...but that will only number the rows - it won't reset when you cross a group, you'll have to query a group at a time.

    点赞 评论

相关推荐 更多相似问题