PostgreSQL 在选择查询中使用 CASE WHEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45496031/
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
PostgreSQL using CASE WHEN in a select query
提问by John Smith
I have a simple table in a cricket database that records the 'runs' each player scores in a game. The fields are: the player's name, the runs they scored in a particular game, whether they were 'out' or 'not out' at the end of the match. Obviously players appear as many times in the table as matches they have played
我在板球数据库中有一个简单的表格，用于记录每个球员在一场比赛中的得分。这些字段是：球员的姓名，他们在特定比赛中的得分，无论他们在比赛结束时“出局”还是“未出局”。显然，玩家出现在表中的次数与他们参加过的比赛一样多
I want to run a query that gives me the average 'runs' per player. In cricket, this is done by taking their total runs and dividing only by the number of times they were 'out', the code I have been trying is something like this:
我想运行一个查询，为我提供每个玩家的平均“运行”。在板球中，这是通过将他们的总跑步次数除以他们“出局”的次数来完成的，我一直在尝试的代码是这样的：
SELECT name,
SUM(runs)/COUNT(CASE WHEN playerout = 'out' THEN name END)
FROM players
GROUP BY name;
The idea of this being that for each player, it sums their runs and divides only by the number of times they were 'out', ignoring the 'notout's. This code doesn't work as it still includes the times they were 'notout' I have tried adding 'ELSE 0' in but an error is returned due to integer and character data types being mixed.
这样做的想法是，对于每个玩家，它会总结他们的运行并仅除以他们“出局”的次数，而忽略“出局”的次数。此代码不起作用，因为它仍然包括它们“notout”的时间，我尝试在其中添加“ELSE 0”，但由于混合了整数和字符数据类型，因此返回错误。
Update
更新
The query needs to add up a player's runs in every game, irrespective of whether they finished out or not out and then divide by the number of times they were out. For example if one player had matches as follows: 1. 10 runs, out 2. 20 runs, out 3. 30 runs, not out Their average would by their total runs (10+20+30) = 60, divided by the number of times they were out (2), so their average would be 30.
该查询需要将玩家在每场比赛中的跑动相加，无论他们是否出局，然后除以他们出局的次数。例如，如果一名球员的比赛如下： 1. 10 次运行，出局 2. 20 次运行，出局 3. 30 次，未出局 他们的平均值将除以他们的总运行次数 (10+20+30) = 60，除以数量他们出局的次数 (2)，因此他们的平均值为 30。
There is probably an obvious solution which I haven't realised - any suggestions?!
可能有一个我没有意识到的明显解决方案 - 有什么建议吗？！
回答by e_i_pi
If you want to report on allplayers irrespective of whether they have been out or not in any match, then you need to define a value for when they have never been out. For the example below, I have used NULL. Doing it this way ensures you don't get a division by zeroerror.
如果您想报告所有球员，而不管他们是否在任何比赛中出局，那么您需要定义他们从未出局的时间。对于下面的示例，我使用了NULL. 这样做可以确保您不会得到零错误除法。
SELECT
name,
CASE WHEN SUM(CASE WHEN playerout = 'out' THEN 1 ELSE 0 END) = 0
THEN NULL
ELSE
SUM(runs)
/SUM(CASE WHEN playerout = 'out' THEN 1 ELSE 0 END)
END AS runs_divided_by_dismissals
FROM players
GROUP BY name;
回答by Ferdinand Gaspar
You can use
您可以使用
SUM(CASE WHEN playerout = 'out' THEN 1 ELSE 0 END)
