MySQL-如何根据另一个SELECT的值进行SELECT [英] MySQL - How to SELECT based on value of another SELECT

问题描述

我有一个看起来像这样的表:

I have a table that looks something like this:

Name    Year   Value
 A      2000     5
 A      2001     3
 A      2002     7
 A      2003     1
 B      2000     6
 B      2001     1
 B      2002     8
 B      2003     2

用户可以根据年份范围进行查询，并返回按名称分组的Value的总和，例如(假设查询的年份为2000-2001):

The user can query based on a range of years, and it will return the sum of Value grouped by Name, as such (assume queried years are 2000 - 2001):

Name   SUM(Value)
 A         8
 B         7

现在，我想插入一个计算所得的字段，该字段输出所有年份的每个名称的值之和与所有值的总和之比.基本上，分别归于A和B的所有值所占的百分比，例如:

Now, I wanted to insert a calculated field that outputs the ratio of the sum of the values of each name for ALL years to the sum of all values. Basically the percentage of all values attributed to A and B, respectively, like:

Name   SUM(Value)   % Of Total
 A         8            0.484      (16 / 33)
 B         7            0.516      (17 / 33)

请注意，即使用户仅查询2000-2001，我仍希望计算使用所有年份的总和.我已经搜索和尝试了几个小时，但我不知道怎么做.我只知道在这样查询的年份中如何求和:

Note that even though the user queried only 2000-2001, I want the calculation to use the sum across all years. I've been searching and experimenting for hours and I cannot figure out how. I only know how to sum across the queried years like so:

SELECT `Name`, SUM(`Value`)/(SELECT SUM(`Value`) FROM `table1`) AS "% of Total"
FROM `table1`
WHERE `Year` BETWEEN 2000 AND 2001
GROUP BY `Name`;

请帮助！我是一个新手，对SQL的了解也不多，因此请澄清所有高级代码.谢谢您的好意.

Please help! I'm very much a novice, and don't understand SQL in much depth, so please clarify any advanced code. Thank you for your kindness.

推荐答案

您可以通过在FROM子句中使用子查询来计算总数(并从中得出所需的百分比):

You can calculate the total (and from that the desired percentage) by using a subquery in the FROM clause:

SELECT Name,
       SUM(Value) AS "SUM(VALUE)",
       SUM(Value) / totals.total AS "% of Total"
FROM   table1,
       (
           SELECT Name,
                  SUM(Value) AS total
           FROM   table1
           GROUP BY Name
       ) AS totals
WHERE  table1.Name = totals.Name
AND    Year BETWEEN 2000 AND 2001
GROUP BY Name;

请注意，子查询没有用于过滤年份的WHERE子句.

Note that the subquery does not have the WHERE clause filtering the years.

这篇关于MySQL-如何根据另一个SELECT的值进行SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！