好的，这是我的数据库结构：
TABLE `books`
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT
`title` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
`author` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
PRIMARY KEY (`id`)
UNIQUE KEY `title` (`title`)
TABLE `chapters`
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT
`book_id` smallint(5) unsigned NOT NULL DEFAULT '0'
`number` smallint(5) unsigned NOT NULL DEFAULT '0'
`title` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
PRIMARY KEY (`id`)
KEY `book_id` (`book_id`) -> Foreign on `books`.`id`
TABLE `pages`
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT
`book_id` smallint(5) unsigned NOT NULL DEFAULT '0'
`chapter_id` smallint(5) unsigned NOT NULL DEFAULT '0'
`number` smallint(5) unsigned NOT NULL DEFAULT '0' // real number, for page 1 of book 1 this has value 1, for page 1 of book 2 this has value 1...
`value` float NOT NULL DEFAULT '0'
PRIMARY KEY (`id`)
KEY `book_id` (`book_id`) -> Foreign on `books`.`id`
KEY `chapter_id` (`chapter_id`) -> Foreign on `chapters`.`id`
现在。。。在PHP页面上，我想显示每本书的平均pages.value，每本书每章的平均pages.value以及每页的单个pages.value：
Book 1 AVG = X
Book 1 Chapter 1 AVG = X
Book 1 Chapter ... AVG = X
Book 1 Chapter N AVG = X
Book 1 Chapter 1 Page 1 VAL = X
Book 1 Chapter 1 Page ... VAL = X
Book 1 Chapter 1 Page N VAL = X
And so on...
当然，使用 3 个查询和一些连接将非常简单，我可以使用 PHP 以编程方式重新格式化符合我需求的数据。但我想知道是否有可能将这些查询（顺便说一下，执行速度非常慢）优化为单个查询，或者通过使用高级语法来减少处理时间。
为您希望用于搜索的列编制索引会有很大帮助。例如：
CREATE INDEX index_name ON table_name(column_name);
完成相同操作的另一种方法是：
ALTER TABLE table_name ADD INDEX ( column_name );
这样，您的查询执行时间将很快...
一种方法是使用 ROLLUP ，但正如 Gordon 指出的那样，这可能不会返回您期望的平均值。这取决于如何定义这些聚合。但是，我怀疑MySQL确实以您所期望的方式定义了这些平均值。这里有一个例子，它简洁地说明了如何回答问题以及如何提出问题（并可能识别这种方法中的缺陷，其他人可以批评）......
CREATE TABLE pages
(page_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,book VARCHAR(20) NOT NULL
,chapter_no INT NOT NULL
,page_no INT NOT NULL
,words INT NOT NULL
,UNIQUE(book,chapter_no,page_no)
);
INSERT INTO pages (book,chapter_no,page_no,words) VALUES
('The Hobbit',1,1,50),
('The Hobbit',1,2,60),
('The Hobbit',1,3,100),
('The Hobbit',1,4,40),
('The Hobbit',2,1,60),
('The Hobbit',2,2,40),
('The Hobbit',2,3,20),
('Oliver Twist',1,1,120),
('Oliver Twist',1,2,30),
('Oliver Twist',1,3,30),
('Oliver Twist',2,1,100),
('Oliver Twist',2,2,50);
SELECT *
FROM pages
ORDER
BY book
,chapter_no
, page_no;
+---------+--------------+------------+---------+-------+
| page_id | book | chapter_no | page_no | words |
+---------+--------------+------------+---------+-------+
| 8 | Oliver Twist | 1 | 1 | 120 |
| 9 | Oliver Twist | 1 | 2 | 30 |
| 10 | Oliver Twist | 1 | 3 | 30 |
| 11 | Oliver Twist | 2 | 1 | 100 |
| 12 | Oliver Twist | 2 | 2 | 50 |
| 1 | The Hobbit | 1 | 1 | 50 |
| 2 | The Hobbit | 1 | 2 | 60 |
| 3 | The Hobbit | 1 | 3 | 100 |
| 4 | The Hobbit | 1 | 4 | 40 |
| 5 | The Hobbit | 2 | 1 | 60 |
| 6 | The Hobbit | 2 | 2 | 40 |
| 7 | The Hobbit | 2 | 3 | 20 |
+---------+--------------+------------+---------+-------+
SELECT book
, chapter_no
, page_no
, AVG(words) words_per_page
FROM pages
GROUP
BY book
, chapter_no
, page_no WITH ROLLUP;
+--------------+------------+---------+----------------+
| book | chapter_no | page_no | words_per_page |
+--------------+------------+---------+----------------+
| Oliver Twist | 1 | 1 | 120.0000 |
| Oliver Twist | 1 | 2 | 30.0000 |
| Oliver Twist | 1 | 3 | 30.0000 |
| Oliver Twist | 1 | NULL | 60.0000 |<-- avg words per page chapter 1
| Oliver Twist | 2 | 1 | 100.0000 |
| Oliver Twist | 2 | 2 | 50.0000 |
| Oliver Twist | 2 | NULL | 75.0000 |<-- avg words per page chapter 2
| Oliver Twist | NULL | NULL | 66.0000 |<-- avg words per page entire book
| The Hobbit | 1 | 1 | 50.0000 |
| The Hobbit | 1 | 2 | 60.0000 |
| The Hobbit | 1 | 3 | 100.0000 |
| The Hobbit | 1 | 4 | 40.0000 |
| The Hobbit | 1 | NULL | 62.5000 |<-- avg words per page chapter 1
| The Hobbit | 2 | 1 | 60.0000 |
| The Hobbit | 2 | 2 | 40.0000 |
| The Hobbit | 2 | 3 | 20.0000 |
| The Hobbit | 2 | NULL | 40.0000 |<-- avg words per page chapter 2
| The Hobbit | NULL | NULL | 52.8571 |<-- avg words per page entire book
| NULL | NULL | NULL | 58.3333 |<-- avg words per page all books
+--------------+------------+---------+----------------+