MySQL - 如何根据另一个SELECT的值进行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

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):

用户可以根据年份进行查询，并返回按名称分组的值之和（假设查询年份为2000年至2001年）：

Name   SUM(Value)
 A         8
 B         7

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:

现在，我想插入一个计算字段，输出所有年份的每个名称的值之和与所有值之和的比率。基本上分别归因于A和B的所有值的百分比，如：

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

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:

请注意，即使用户仅查询2000-2001，我希望计算使用所有年份的总和。我一直在寻找和试验几个小时，我无法弄清楚如何。我只知道如何在所查询的年份之间总结如下：

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

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.

请帮忙！我是一个非常新手，并且不太了解SQL，所以请澄清任何高级代码。谢谢你的好意。

3 个解决方案

#1

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

您可以使用FROM子句中的子查询来计算总计（以及所需的百分比）：

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;

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

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

#2

SELECT x.name, x.summary, (x.summary / COUNT(*)) as percents_of_total
FROM tbl t
INNER JOIN 
(SELECT name, SUM(value) as summary
FROM tbl
WHERE year BETWEEN 2000 AND 2001
GROUP BY name) x ON x.name = t.name
GROUP BY x.name, x.summary

#3

If you want to SELECT based on the value of another SELECT, then you probably want a "subselect":

如果你想根据另一个SELECT的值进行SELECT，那么你可能想要一个“subselect”：

http://beginner-sql-tutorial.com/sql-subquery.htm

For example, (from the link above):

例如，（来自上面的链接）：

You want the first and last names from table "student_details" ...

你想要表“student_details”中的名字和姓氏......
But you only want this information for those students in "science" class:

但是你只想为“科学”课程的学生提供这些信息：
```
SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science'); 
```

Frankly, I'm not sure this is what you're looking for or not ... but I hope it helps ... at least a little...

坦率地说，我不确定这是你在寻找与否......但我希望它有所帮助......至少有一点......

IMHO...

恕我直言...

#1