BOOK_MASTER
book_master_id    book_name
1                 Book 1
2                 Book 2
3                 Book 3
4                 Book 4

AUTHOR
author_id         author_name 
22                Jim 
23                Roger   
24                Andrew 

BOOK_AUTHOR
book_master_id    author_id
1                 22
1                 23
2                 23
3                 24
4                 23
4                 24

您应该在数据库模式上设置正确的约束,但这会给您一个想法.

使用此数据库结构,您可以使用以下查询来获得所需的结果：

SELECT
    a.author_name,COUNT(*) as number_of_books
From
    author a
    JOIN book_author ba ON a.id = ba.author_id
group by
    a.author_name
ORDER BY number_of_books DESC

编写一个脚本,将当前书籍表的author_id逗号分隔列表移动到新的author_book表中应该是相当简单的,下面的内容可能会起作用：

<?PHP
$query = "SELECT book_master_id,author_id From books";
$result = MysqL_query($query);

while ($row = MysqL_fetch_row($result)) {
    $values = array();
    foreach (explode(',',$row['author_id']) as $authorId) {
        $values[] = "(" . $row['book_master_id'] . "," . $authorId . ")";
    }

    $query = "INSERT IGNORE INTO book_author (book_master_id,author_id) VALUES ";
    $query .= implode(',$values);

    MysqL_query($query);
}

请注意,我没有测试这段代码,你应该首先在测试数据库上试一试,看看它是否真的能够完成它应该做的事情.此外,如果您有大量数据,则此脚本可能需要一些时间才能执行.最后,也许有一个SQL查询可以做到这一点,但这是第一个想到的解决方案.