MySQL:多个子查询的总和 [英] MySQL: Sum of multiple subqueries

问题描述

我需要通过多个子查询的结果 SUM 对 MySQL 查询进行排序.

I need to order a MySQL query by the resulting SUM of multiple subqueries.

以下是我要执行的操作的一些示例代码:

Here's some example code for what I'm trying to do:

SELECT  ...
        (SELECT SUM(
           (SELECT one_result ... LIMIT 1) as plays1,
           (SELECT one_result ... LIMIT 1) as plays2,
           (SELECT one_result ... LIMIT 1) as plays3
        )) as total_plays
FROM plays
ORDER BY total_plays

基本上，我需要运行三个子查询，每个子查询都返回一个整数.

Basically, I need to run three subqueries that'll each return an integer.

我需要通过这些整数的 SUM() 对整个查询进行排序.

I need to order the entire query by the SUM() of these integers.

当我尝试运行此查询时，出现语法错误.

When I try to run this query I get a syntax error.

谁能告诉我对多个子查询求和的正确语法是什么?

Could someone let me know what the proper syntax is for summing multiple subqueries?

我也试过了:

SELECT  ...
        (SELECT one_result ... LIMIT 1) as plays1,
        (SELECT one_result ... LIMIT 1) as plays2,
        (SELECT one_result ... LIMIT 1) as plays3
        SUM(plays1, plays3, plays3) as total_plays
FROM plays
ORDER BY total_plays

编辑

@JoeC 和@SATSON 提供了类似的答案来解决这个问题.这是我的(工作)真实查询，以防这有助于其他人开始他们自己的查询:

@JoeC and @SATSON provided similar answers that solved this. Here's my (working) real query in case this helps anyone else get started on their own query:

````

SELECT  song.title as title,
        song.file_name as unique_name,
        song.artist as artist,
       (SELECT difficulty FROM chart WHERE id = song.easy_chart ORDER BY scoring_version ASC LIMIT 1) as easy_difficulty,
       (SELECT difficulty FROM chart WHERE id = song.hard_chart ORDER BY scoring_version ASC LIMIT 1) as hard_difficulty,
       (SELECT difficulty FROM chart WHERE id = song.master_chart ORDER BY scoring_version ASC LIMIT 1) as master_difficulty,
       (plays.easy_plays + plays.hard_plays + plays.master_plays) as total_plays
FROM song
INNER JOIN (SELECT parent_song_id,
               (SELECT global_plays ORDER BY scoring_version ASC LIMIT 1) as easy_plays,
               (SELECT global_plays ORDER BY scoring_version ASC LIMIT 1) as hard_plays,
               (SELECT global_plays ORDER BY scoring_version ASC LIMIT 1) as master_plays
       FROM chart) as plays
ON plays.parent_song_id = song.id
ORDER BY total_plays DESC
LIMIT 9
OFFSET 0

````

推荐答案

嗯，怎么样

SELECT *, plays1 + plays2 + plays3 as total_play from 
(SELECT  ...
        (SELECT one_result ... LIMIT 1) as plays1,
        (SELECT one_result ... LIMIT 1) as plays2,
        (SELECT one_result ... LIMIT 1) as plays3
FROM plays)
ORDER BY total_plays

这篇关于MySQL:多个子查询的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！