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
2
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
0
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
2
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
0
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列中为您提供逗号分隔的值列表,然后按程序提取数据。