SQL Query如何计算单行中的不同值

I am sure there must be a relatively straightforward way to do this, but it is escaping me at the moment. Suppose I have a SQL table like this:

我相信必须有一个相对简单的方法来做到这一点,但它现在正在逃避我。假设我有一个这样的SQL表:

+-----+-----+-----+-----+
|  A  |  B  |  C  |  D  |
+=====+=====+=====+=====+
|  a  |  b  |  3  | 100 |  << a,b
+-----+-----+-----+-----+
|  a  |  c  |  3  | 60  |  << a,c
+-----+-----+-----+-----+
|  a  |  b  |  4  | 50  |  << a,b
+-----+-----+-----+-----+
|  a  |  b  |  5  | 30  |  << a,b
+-----+-----+-----+-----+
|  d  |  b  |  3  | 35  |  << d,b
+-----+-----+-----+-----+
|  a  |  c  |  2  | 40  |  << a,c
+-----+-----+-----+-----+

Now, I want to know how many times each combination of values for columns A and B appear, then it can hold data column D based on grouping by column C in single row. So, in this example, I want an output something like this:

现在,我想知道列A和B的每个值组合出现多少次,然后它可以根据单行C列的分组来保存数据列D.所以,在这个例子中,我想要一个像这样的输出:

+-----+-----+-----+-----+-----+
|  A  |  B  |  C3 |  C4 |  C5 |
+=====+=====+=====+=====+=====+
|  a  |  b  | 100 | 50  | 30  | << a,b
+-----+-----+-----+-----+-----+
|  a  |  c  | 60  |  0  |  0  | << a,c
+-----+-----+-----+-----+-----+
|  d  |  b  | 35  |  0  |  0  | << d,b
+-----+-----+-----+-----+-----+

What would be the SQL to determine that? I feel like this must not be a very uncommon thing to want to do.

确定这一点的SQL是什么?我觉得这绝不是一件非常不寻常的事情。

Thanks!

2 个解决方案

#1

You can use:

您可以使用:

SELECT A, B,
       sum(if(C=3, D, NULL)) as C3,
       sum(if(C=4, D, NULL)) as C4,
       sum(if(C=5, D, NULL)) as C5
  FROM yourTable
GROUP BY A, B;

#2

You can achieve a similar result by using GROUP_CONCAT (see docs), which gives you a comma-separated list of values in column D and column C and then extract your data by program.

您可以使用GROUP_CONCAT(请参阅docs)获得类似的结果,它在D列和C列中为您提供逗号分隔的值列表,然后按程序提取数据。

#1