计数问题 [英] Counting problem
问题描述
大家好
我在这里需要你的帮助，我一直在努力解决这个问题几个小时，而且这是让我疯狂的b $ b 。这是另一个计数问题。
让我们说我有一个朋友。表看起来像这样：
+ ------------- + ---------------- + - ------- +
|镇|名称|眼睛|
+ ------------- + ---------------- + -------- +
|巴黎|尼古拉斯|蓝色|
|巴黎|克莱尔|蓝色|
|巴黎|西蒙|棕色|
|巴黎|玛丽|黑色|
|纽约|杰森|蓝色|
|纽约|弗兰克|绿色|
|纽约|艾米|蓝色|
+ ------------- + ---------------- + -------- +
我想要的是知道有特定眼睛颜色的朋友的数量
住在每个城镇，如下：
+ ------------ + ------ + ------- + ------- + ------- +
|镇|蓝色|棕色|绿色|黑色|
+ ------------ + ------ + ------- + ------- + --- ---- +
|纽约| 2 | 0 | 1 | 0 |
|巴黎| 2 | 1 | 0 | 1 |
+ ------------ + ------ + ------- + ------- + --- ---- +
我在掌握JOIN时总是非常糟糕，而且我在这里完全失去了b
。 />
那里有慈善灵魂可以告诉我如何解决这个问题吗？
非常感谢你。
Tom
-
Thomas Guignard
声学和电磁学实验室
瑞士联邦学院技术，洛桑
Hi all
I need your help here, I''ve been trying to solve this for hours and it''s
driving me crazy. It''s yet another counting problem.
Let''s say I have a "friends" table looking like this:
+-------------+----------------+--------+
| Town | Name | Eyes |
+-------------+----------------+--------+
| Paris | Nicolas | blue |
| Paris | Claire | blue |
| Paris | Simon | brown |
| Paris | Marie | black |
| NY | Jason | blue |
| NY | Frank | green |
| NY | Amy | blue |
+-------------+----------------+--------+
What I want is to know the number of friends with a particular eye color
living in each town, like this:
+------------+------+-------+-------+-------+
| Town | blue | brown | green | black |
+------------+------+-------+-------+-------+
| NY | 2 | 0 | 1 | 0 |
| Paris | 2 | 1 | 0 | 1 |
+------------+------+-------+-------+-------+
I''ve always been very bad in mastering JOINs, and here I''m at a complete
loss.
Is there a charitable soul out there that can show me how to solve this?
Thank you very much.
Tom
--
Thomas Guignard
Acoustics and Electromagnetism Lab
Swiss Federal Institute of Technology, Lausanne
推荐答案
Thomas Guignard写道：
Thomas Guignard wrote:
大家好
我需要你的帮助，我一直试图解决这个问题几个小时，这让我发疯了。这是另一个计数问题。
让我们说我有一个朋友。表看起来像这样：
+ ------------- + ---------------- + ----- --- +
|镇|名称|眼睛|
+ ------------- + ---------------- + -------- +
|巴黎|尼古拉斯|蓝色|
|巴黎|克莱尔|蓝色|
|巴黎|西蒙|棕色|
|巴黎|玛丽|黑色|
|纽约|杰森|蓝色|
|纽约|弗兰克|绿色|
|纽约|艾米|蓝色|
+ ------------- + ---------------- + -------- +
我想要知道生活在每个城镇的特定眼睛颜色的朋友的数量，如下：
+ --------- --- + ------ + ------- + ------- + ------- +
|镇|蓝色|棕色|绿色|黑色|
+ ------------ + ------ + ------- + ------- + ------- +
|纽约| 2 | 0 | 1 | 0 |
|巴黎| 2 | 1 | 0 | 1 |
+ ------------ + ------ + ------- + ------- + ------- +
我在掌握JOIN方面一直非常糟糕，而且我在这里完全失去了。
是否有一个慈善的灵魂出局那可以告诉我如何解决这个问题吗？
非常感谢。
Tom
Hi all
I need your help here, I''ve been trying to solve this for hours and it''s
driving me crazy. It''s yet another counting problem.
Let''s say I have a "friends" table looking like this:
+-------------+----------------+--------+
| Town | Name | Eyes |
+-------------+----------------+--------+
| Paris | Nicolas | blue |
| Paris | Claire | blue |
| Paris | Simon | brown |
| Paris | Marie | black |
| NY | Jason | blue |
| NY | Frank | green |
| NY | Amy | blue |
+-------------+----------------+--------+
What I want is to know the number of friends with a particular eye color
living in each town, like this:
+------------+------+-------+-------+-------+
| Town | blue | brown | green | black |
+------------+------+-------+-------+-------+
| NY | 2 | 0 | 1 | 0 |
| Paris | 2 | 1 | 0 | 1 |
+------------+------+-------+-------+-------+
I''ve always been very bad in mastering JOINs, and here I''m at a complete
loss.
Is there a charitable soul out there that can show me how to solve this?
Thank you very much.
Tom
又快又脏......
SELECT Town，COUNT（IF（Eyes =''blue''，1，NULL））AS blue，....来自朋友
GROUP BY Town;
必须有更优雅的方式
布鲁斯
quick and dirty...
SELECT Town, COUNT(IF(Eyes=''blue'',1,NULL)) AS blue, .... FROM friends
GROUP BY Town;
There must be a more elegant way
Bruce
Thomas Guignard写道：
Thomas Guignard wrote:
大家好
我需要你的帮助，我一直在努力解决这个问题几个小时，这让我发疯了。这是另一个计数问题。
让我们说我有一个朋友。表看起来像这样：
+ ------------- + ---------------- + ----- --- +
|镇|名称|眼睛|
+ ------------- + ---------------- + -------- +
|巴黎|尼古拉斯|蓝色|
|巴黎|克莱尔|蓝色|
|巴黎|西蒙|棕色|
|巴黎|玛丽|黑色|
|纽约|杰森|蓝色|
|纽约|弗兰克|绿色|
|纽约|艾米|蓝色|
+ ------------- + ---------------- + -------- +
我想要知道生活在每个城镇的特定眼睛颜色的朋友的数量，如下：
+ --------- --- + ------ + ------- + ------- + ------- +
|镇|蓝色|棕色|绿色|黑色|
+ ------------ + ------ + ------- + ------- + ------- +
|纽约| 2 | 0 | 1 | 0 |
|巴黎| 2 | 1 | 0 | 1 |
+ ------------ + ------ + ------- + ------- + ------- +
我在掌握JOIN方面一直非常糟糕，而且我在这里完全失去了。
是否有一个慈善的灵魂出局那可以告诉我如何解决这个问题吗？
非常感谢。
Tom
Hi all
I need your help here, I''ve been trying to solve this for hours and it''s
driving me crazy. It''s yet another counting problem.
Let''s say I have a "friends" table looking like this:
+-------------+----------------+--------+
| Town | Name | Eyes |
+-------------+----------------+--------+
| Paris | Nicolas | blue |
| Paris | Claire | blue |
| Paris | Simon | brown |
| Paris | Marie | black |
| NY | Jason | blue |
| NY | Frank | green |
| NY | Amy | blue |
+-------------+----------------+--------+
What I want is to know the number of friends with a particular eye color
living in each town, like this:
+------------+------+-------+-------+-------+
| Town | blue | brown | green | black |
+------------+------+-------+-------+-------+
| NY | 2 | 0 | 1 | 0 |
| Paris | 2 | 1 | 0 | 1 |
+------------+------+-------+-------+-------+
I''ve always been very bad in mastering JOINs, and here I''m at a complete
loss.
Is there a charitable soul out there that can show me how to solve this?
Thank you very much.
Tom
又快又脏......
SELECT Town，COUNT（IF（Eyes =''blue''，1，NULL））AS blue，....来自朋友
GROUP BY Town;
必须有一个更优雅的方式
布鲁斯
quick and dirty...
SELECT Town, COUNT(IF(Eyes=''blue'',1,NULL)) AS blue, .... FROM friends
GROUP BY Town;
There must be a more elegant way
Bruce
Bruce Wolk< fa**@not-a-real-address.net>在消息新闻中写道：< QB ******************* @ newssvr29.news.prodigy .com> ...
Bruce Wolk <fa**@not-a-real-address.net> wrote in message news:<QB*******************@newssvr29.news.prodigy .com>...
Thomas Guignard写道：
Thomas Guignard wrote:
大家好
我在这里需要你的帮助，我一直试图解决这个问题几个小时，这让我发疯了。这是另一个计数问题。
让我们说我有一个朋友。表看起来像这样：
+ ------------- + ---------------- + ----- --- +
|镇|名称|眼睛|
Hi all
I need your help here, I''ve been trying to solve this for hours and it''s
driving me crazy. It''s yet another counting problem.
Let''s say I have a "friends" table looking like this:
+-------------+----------------+--------+
| Town | Name | Eyes |
+ ------------- + ---------------- + -------- +
+-------------+----------------+--------+
|巴黎|尼古拉斯|蓝色|
|巴黎|克莱尔|蓝色|
|巴黎|西蒙|棕色|
|巴黎|玛丽|黑色|
|纽约|杰森|蓝色|
|纽约|弗兰克|绿色|
|纽约|艾米|蓝色|
+ ------------- + ---------------- + -------- +
我想要知道生活在每个城镇的特定眼睛颜色的朋友的数量，如下：
+ --------- --- + ------ + ------- + ------- + ------- +
|镇|蓝色|棕色|绿色|黑色|
| Paris | Nicolas | blue |
| Paris | Claire | blue |
| Paris | Simon | brown |
| Paris | Marie | black |
| NY | Jason | blue |
| NY | Frank | green |
| NY | Amy | blue |
+-------------+----------------+--------+
What I want is to know the number of friends with a particular eye color
living in each town, like this:
+------------+------+-------+-------+-------+
| Town | blue | brown | green | black |
+ ------------ + ------ + ------- + ------- + --- ---- +
+------------+------+-------+-------+-------+
|纽约| 2 | 0 | 1 | 0 |
|巴黎| 2 | 1 | 0 | 1 |
+ ------------ + ------ + ------- + ------- + ------- +
我在掌握JOIN方面一直非常糟糕，而且我在这里完全失去了。
是否有一个慈善的灵魂出局在那里可以告诉我如何解决这个问题？
| NY | 2 | 0 | 1 | 0 |
| Paris | 2 | 1 | 0 | 1 |
+------------+------+-------+-------+-------+
I''ve always been very bad in mastering JOINs, and here I''m at a complete
loss.
Is there a charitable soul out there that can show me how to solve this?
SELECT DISTINCT TOWN，
SUM（IF（EYES =''） blue''，1,0））为''Blue''，
SUM（IF（EYES =''brown''，1,0））为''Brown''，
SUM（IF（EYES =''black''，1,0））为''Black''，
SUM（IF（EYES ='''灰色''， 1,0））''灰色''，
SUM（IF（EYES =''green''，1,0））为''Green''，
SUM（CASE
WHY EYES =''blue''那么0
当眼睛=''棕色''那么0
WHEN EYES =''black''那么0
当眼睛=''灰色'时那么0
当眼睛=''绿色'时那么0
ELSE 1
END）as''Other''
来自朋友们
GROUP BY town;
SELECT DISTINCT TOWN,
SUM(IF(EYES=''blue'',1,0)) as ''Blue'',
SUM(IF(EYES=''brown'',1,0)) as ''Brown'',
SUM(IF(EYES=''black'',1,0)) as ''Black'',
SUM(IF(EYES=''gray'',1,0)) as ''Gray'',
SUM(IF(EYES=''green'',1,0)) as ''Green'',
SUM(CASE
WHEN EYES = ''blue'' THEN 0
WHEN EYES = ''brown'' THEN 0
WHEN EYES = ''black'' THEN 0
WHEN EYES = ''gray'' THEN 0
WHEN EYES = ''green'' THEN 0
ELSE 1
END ) as ''Other''
FROM friends
GROUP BY town;
这篇关于计数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
