分组但保留所有非NULL值

Let's say I have the table:

假设我有桌子：

 ID |  Name | Intolerance
  1 |   Amy |     Lactose
  2 | Brian |     Lactose
  3 |   Amy |      Gluten

And I run this SQL query:

我运行这个SQL查询：

SELECT 
    Name,
    CASE 
       WHEN Intolerance = 'Lactose' 1
    END AS Lactose,
    CASE 
       WHEN Intolerance = 'Gluten' 1
    END AS Gluten
FROM 
    Table

I get:

我明白了：

  Name   | Lactose | Gluten
  -------+---------+--------
  Amy    |    1    |
  Amy    |         |   1
  Brian  |    1    |

But if I try to add "GROUP BY Name", Amy won't have a 1 in both columns, because GROUP BY only selects the last row of each Name. What I want to get instead is this:

但是如果我尝试添加“GROUP BY Name”，Amy在两列中都不会有1，因为GROUP BY只选择每个Name的最后一行。我想要得到的是：

 Name | Lactose | Gluten
------+---------+---------
  Amy |       1 |      1
Brian |       1 |

How can I get that? Is there perhaps a more efficient way to summarize who's allergic to what from the same input? Thanks in advance.

我怎么能得到它？是否有更有效的方法来总结谁对同一输入过敏？提前致谢。

3 个解决方案

#1

When using a GROUP BY then the aggregate functions can be used for columns that aren't in the GROUP BY.

使用GROUP BY时，聚合函数可用于不在GROUP BY中的列。

In this case I assume you want to use MAX, to get only a 1 or a NULL.

在这种情况下，我假设您要使用MAX，只获得1或NULL。

SUM or COUNT can also be used to surround a CASE WHEN.
But then those would return a total.

SUM或COUNT也可用于包围CASE WHEN。但那会有回报。

SELECT 
 Name,
 MAX(CASE WHEN Intolerance = 'Lactose' THEN 1 END) AS Lactose,
 MAX(CASE WHEN Intolerance = 'Gluten' THEN 1 END) AS Gluten
FROM Table
GROUP BY Name
ORDER BY Name

Or if you don't want to see NULL's?
Then let the CASE return a varchar instead of a number.

或者如果你不想看到NULL？然后让CASE返回varchar而不是数字。

SELECT 
 Name,
 MAX(CASE WHEN Intolerance = 'Lactose' THEN '1' ELSE '' END) AS Lactose,
 MAX(CASE WHEN Intolerance = 'Gluten' THEN '1' ELSE '' END) AS Gluten
FROM Table
GROUP BY Name
ORDER BY Name

#2

I think what you need is the sum of the number of number of intolerances for each person. Also, put a ELSE so the value is 0 or 1:

我认为你需要的是每个人不容忍人数的总和。另外，放一个ELSE使值为0或1：

SELECT 
     Name,
     SUM(CASE WHEN Intolerance = 'Lactose' THEN 1 ELSE 0 END) AS Lactose,
     SUM(CASE WHEN Intolerance = 'Gluten' THEN 1 ELSE 0 END) AS Gluten
FROM Table
GROUP BY Name
ORDER BY Name

#3

I feel that each time I encounter a question like that, it's because a no proper amount of thinking on design was allowed to the project.

我觉得每次遇到这样的问题，都是因为对项目没有适当的设计思考。

To put it simply : you are trying to move data to columns. This is what your application layer is for ! Not the database. People tend to mix what databases are for with what application / UI layer and vice versa are for !

简而言之：您正在尝试将数据移动到列。这就是您的应用程序层的用途！不是数据库。人们倾向于将数据库用于什么应用程序/ UI层，反之亦然！

And each time it happens, I see people reaping their mind to answer because that's the point here : answer the question no matter what. Don't question what the OP want to do, give him the answer...

每次发生这种情况时，我都会看到人们在回答这个问题，因为这就是重点：无论如何回答这个问题。不要质疑OP想要做什么，给他答案......

Sorry for that, I am just a little bit pissed.

对不起，我有点生气。

My solution : Keep your original query and do the aesthetic on your UI / application layer side. You probably have a IList inside each Person. Just fill them and give the UI the opportunity to display them however it wants. Because that's what you're asking the database to do : aesthetics.

我的解决方案：保留原始查询并在UI /应用程序层进行审美。你可能每个人都有一个IList。只需填写它们，并为UI提供显示它们的机会。因为这就是你要求数据库做的事：美学。

#1