CREATE TABLE IF NOT EXISTS kds(
id int( 11 ) NOT NULL AUTO_INCREMENT ,
question varchar( 100 ) ,
book_id int( 11 ) NOT NULL default 0,
ordinal int( 11 ) NOT NULL default 0,
PRIMARY KEY ( id )
) ENGINE = MYISAM default CHARSET = utf8;insert into kds (question,book_id,ordinal) values('text', 1, 0);
insert into kds (question,book_id,ordinal) values('text', 1, 0);
insert into kds (question,book_id,ordinal) values('text', 1, 0);
insert into kds (question,book_id,ordinal) values('text', 2, 0);
insert into kds (question,book_id,ordinal) values('text', 2, 0);
insert into kds (question,book_id,ordinal) values('text', 3, 0);
insert into kds (question,book_id,ordinal) values('text', 3, 0);
insert into kds (question,book_id,ordinal) values('text', 1, 0);
insert into kds (question,book_id,ordinal) values('text', 1, 0);
insert into kds (question,book_id,ordinal) values('text', 2, 0);
insert into kds (question,book_id,ordinal) values('text', 2, 0);
insert into kds (question,book_id,ordinal) values('text', 4, 0);
insert into kds (question,book_id,ordinal) values('text', 4, 0);
insert into kds (question,book_id,ordinal) values('text', 3, 0);
insert into kds (question,book_id,ordinal) values('text', 5, 0);表结构如上,现在想通过一条SQl数据实现如下功能:
更新字段ordinal,使得每本书(book_id区分)的ordinal都是连续递增的,不同的书之间的ordinal计数互不干扰。
期望结果数据应该是:| id | question | book_id | ordinal |
+----+----------+---------+---------+
| 1 | text | 1 | 1 |
| 2 | text | 1 | 2 |
| 3 | text | 1 | 3 |
| 4 | text | 2 | 1 |
| 5 | text | 2 | 2 |
| 6 | text | 3 | 1 |
| 7 | text | 3 | 2 |
| 8 | text | 1 | 4 |
| 9 | text | 1 | 5 |
| 10 | text | 2 | 3 |
| 11 | text | 2 | 4 |
| 12 | text | 4 | 1 |
| 13 | text | 4 | 2 |
| 14 | text | 3 | 3 |
| 15 | text | 5 | 1 |
+----+----------+---------+---------+这个SQL该怎么写呢?赐教
解决方案 »
- 请教mysql数据同步问题
- 求教句sql
- 请教ORDER BY的查询优化:如何解决"Using temporary; Using filesort"
- 加个引号和不加引号我发现区别很多 不明白
- Lock wait timeout exceeded; try restarting transaction
- 连接数据库
- 数据倒入倒出问题
- 请问:如何在delphi中编写能动态地更改mysql和sybase两种数据库的用户名和密码的程序??
- 我如何光把数据库里面的表定义倒出来呢?。。。。
- 建表总是提示1064,不知哪里出了问题
- 不同表之间如何查询,,比如table_1 table_2 table_3我想查询这3个表之间的数据,,怎么查
- Found block with too small length(求救)
(SELECT A.ID,A.question,A.book_id,COUNT(B.ID) AS ordinal FROM kds A INNER JOIN KDS B ON A.BOOK_ID=B.BOOK_ID AND A.ID>=B.ID GROUP BY A.ID,A.question,A.book_id
) B1
ON A1.book_id=B1.BOOK_ID
SET A1.ordinal=B1.ordinal
您的SQL好像落下了一部分,我自己填上了UPDATE kds A1 INNER JOIN
(SELECT A.id,A.question,A.book_id,COUNT(B.id) AS ordinal FROM kds A INNER JOIN kds B ON A.book_id=B.book_id AND A.id>=B.id GROUP BY A.id,A.question,A.book_id
) B1
ON A1.book_id=B1.book_id
SET A1.ordinal=B1.ordinal
where A1.id = B1.id
UPDATE kds A1 INNER JOIN
(SELECT A.id,A.question,A.book_id,COUNT(B.id) AS ordinal FROM kds A INNER JOIN kds B ON A.book_id=B.book_id AND A.id>=B.id GROUP BY A.id,A.question,A.book_id
) B1
ON A1.book_id=B1.book_id
UPDATE kds A1 INNER JOIN
(SELECT A.id,A.question,A.book_id,COUNT(B.id) AS ordinal FROM kds A INNER JOIN kds B ON A.book_id=B.book_id AND A.id>=B.id GROUP BY A.id,A.question,A.book_id
) B1
ON A1.book_id=B1.book_id
SET A1.ordinal=B1.ordinal and A1.id = B1.id