mysql子查询视图_MySQL子查询，视图和过程;哪个(如果有的话)是正确的？

我写了一个查询，其中包含一组子查询：

SELECT

`board_id`,

`post_count`,

ROUND(`post_age_avg`, 3) as `post_age_avg`,

ROUND(`post_rating_avg`, 3) as `post_rating_avg`,

ROUND(`board_age`, 3) as `board_age`,

ROUND(1 - (`post_age_avg` / `board_age`), 3) AS `board_usage`,

ROUND((`post_count` / `board_age`) * `post_rating_avg`, 3) AS `board_rating`

FROM

(SELECT

`board_id`,

(SELECT COUNT(*) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`) AS `post_count`,

(SELECT AVG(TIME_TO_SEC(TIMEDIFF(NOW(), `post`.`created_on`)) / 3600) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`) as `post_age_avg`,

(SELECT AVG(`rating`) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`) AS `post_rating_avg`,

TIME_TO_SEC(TIMEDIFF(NOW(), `board`.`created_on`)) / 3600 AS `board_age`

FROM `board`) AS `board_stats`现在，尽管我对开展优化或以其他方式改进此查询的建议持开放态度，但我的问题与我以何种方式存储此查询以供将来使用有关。

根据CREATE VIEW上的MySQL手册页，视图会很棒：

The SELECT statement cannot contain a sub-query in the FROM clause.

所以我把这个查询封装在一个过程中。工作正常，呼吁：

CALL get_board_stats();但是，我很快发现，使用过程的灵活性有限(读取为零)，就将结果用作子查询而言。与其他类似的问题一样，我发现：

SELECT * FROM (CALL get_board_stats()) AS `board_stats`;并且其任何置换在语法上都是无效的。

所以我的问题是;我怎样才能实现(如果可能的话)这个查询可以被存储起来，以便在随后的查询中作为“虚拟表”使用，从而可以执行如下操作：

SELECT * FROM /* give_me_board_stats_somehow() */ WHERE ...

Alrighty @OMG Ponies，这是我正在使用的最终版本(现在)，因为它产生相同的结果集，具有相同的数值精度。它与你的非常相似，除了我省略JOIN以支持(再一次)子查询，但这次不是派生表中的列。有些东西告诉我这样做效率不高(例如与JOIN解决方案相比)，或许您可以从中看出一些亮点：

SELECT

`board`.`board_id`,

(SELECT COUNT(*) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`) AS `post_count`,

ROUND((SELECT AVG(TIME_TO_SEC(TIMEDIFF(NOW(), `post`.`created_on`)) / 3600) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`), 3) AS `post_age_avg`,

ROUND((SELECT AVG(`rating`) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`), 3) AS `post_rating_avg`,

ROUND(TIME_TO_SEC(TIMEDIFF(NOW(), `board`.`created_on`)) / 3600, 3) AS `board_age`,

ROUND(1 - ((SELECT AVG(TIME_TO_SEC(TIMEDIFF(NOW(), `post`.`created_on`)) / 3600) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`) / (TIME_TO_SEC(TIMEDIFF(NOW(), `board`.`created_on`)) / 3600)), 3) AS `board_usage`,

ROUND(((SELECT COUNT(*) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`) / (TIME_TO_SEC(TIMEDIFF(NOW(), `board`.`created_on`)) / 3600)) * (SELECT AVG(`rating`) FROM `post` WHERE `post`.`board_id` = `board`.`board_id`), 3) AS `board_rating`

FROM `board`(我会发布一些比这个更好的格式化的文件，但是Workbench的美化效果很差劲，我没有努力：P) sup>

出于某种原因，您的解决方案继续为board_usage和board_rating提供不正确的结果。