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该怎么写呢?赐教
解决方案 »
- 怎么向原字符串字段追加字符串?sql怎样么?
- 如果查看SQL执行错误日志?请大家看看。
- 关于分页
- 在大连怎么找个兼职的机会?
- 如何查出一个表中记录数 为20的记录?
- 批量update问题
- 请问,mysql,有什么办法能够缓冲不紧急的query,先执行一些必需的query。
- MYSQL怎么连这样的语句都不支持,大家帮忙看看
- jbuilder5中datebase Pilot连接mysql的jdbc的问题。
- 问个sql语句
- 不同表之间如何查询,,比如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