MySQL - 如何根据另一个 SELECT 的值进行 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9970495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - How to SELECT based on value of another SELECT
提问by John
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 的理解不是很深入，所以请澄清任何高级代码。谢谢你的好意。
回答by kad81
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 子句。
回答by paulsm4
If you want to SELECTbased on the value of another SELECT, then you probably want a "subselect":
如果您想SELECT基于 another 的值SELECT，那么您可能需要一个“子选择”：
http://beginner-sql-tutorial.com/sql-subquery.htm
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" ...
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');
您想要表“student_details”中的名字和姓氏...
但是您只需要“科学”类学生的这些信息：
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...
恕我直言...
回答by zerkms
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
