英文:
How to insert group by results into columns of another table
问题
我有一个像这样的表格：
表格 A：
| 成绩 | 状态 |
|---|---|
| 10 | 一 |
| 2 | 二 |
| 4 | 一 |
| 1 | 三 |
| 3 | 三 |
| 4 | 一 |
我有另一个表格，我想是这样的：
表格 B：
| 平均(成绩) | 一 | 二 | 三 |
|---|---|---|---|
| 4 | 3 | 1 | 2 |
我该如何操作？谢谢。
英文:
I have a table like:
table A:
| grade | status |
|---|---|
| 10 | one |
| 2 | two |
| 4 | one |
| 1 | three |
| 3 | three |
| 4 | one |
and I have another table that I would be like this :
table B:
| avg(grade) | one | two | three |
|---|---|---|---|
| 4 | 3 | 1 | 2 |
How should I do this? thank you.
答案1
得分: 1
在mysql中，可以按照以下方式进行操作：这是mysql的DB-FIDDLE
INSERT INTO B(avg_grade, one, two, three)
SELECT CAST(AVG(grade) AS UNSIGNED) as avg_grade,
SUM(CASE WHEN status = 'one' THEN 1 ELSE 0 END) as one,
SUM(CASE WHEN status = 'two' THEN 1 ELSE 0 END) as two,
SUM(CASE WHEN status = 'three' THEN 1 ELSE 0 END) as three
FROM A
WHERE status IN ('one', 'two', 'three');
在postgresql中，可以按照以下方式进行操作：这是postgresql的DB-FIDDLE
INSERT INTO B(avg_grade, one, two, three)
SELECT CAST(AVG(grade) AS integer) as avg_grade,
SUM(CASE WHEN status = 'one' THEN 1 ELSE 0 END) as one,
SUM(CASE WHEN status = 'two' THEN 1 ELSE 0 END) as two,
SUM(CASE WHEN status = 'three' THEN 1 ELSE 0 END) as three
FROM A
WHERE status IN ('one', 'two', 'three');
上述查询将给出以下预期输出：
| avg(grade) | one | two | three |
|---|---|---|---|
| 4 | 3 | 1 | 2 |
如果对于列 avg(grade) 你可以接受小数值，你可以将其限制为两位小数，如 ROUND(AVG(grade), 2) as avg_grade,
在这种情况下；mysql和postgresql的查询将相同，如下：
INSERT INTO B(avg_grade, one, two, three)
SELECT ROUND(AVG(grade), 2) as avg_grade,
SUM(CASE WHEN status = 'one' THEN 1 ELSE 0 END) as one,
SUM(CASE WHEN status = 'two' THEN 1 ELSE 0 END) as two,
SUM(CASE WHEN status = 'three' THEN 1 ELSE 0 END) as three
FROM A
WHERE status IN ('one', 'two', 'three');
这将以如下结果返回：
|avg(grade)| one | two | three |
| -------- | -------- | -------- |--------|
| 4.00 | 3 | 1 | 2 |
英文:
You can do it as following in mysql : Here is a DB-FIDDLE for mysql
INSERT INTO B(avg_grade, one, two, three)
SELECT CAST(AVG(grade) AS UNSIGNED) as avg_grade,
SUM(CASE WHEN status = 'one' THEN 1 ELSE 0 END) as one,
SUM(CASE WHEN status = 'two' THEN 1 ELSE 0 END) as two,
SUM(CASE WHEN status = 'three' THEN 1 ELSE 0 END) as three
FROM A
WHERE status IN ('one', 'two', 'three');
The following shall work in postgresql : Here is a DB-FIDDLE for postgresql
INSERT INTO B(avg_grade, one, two, three)
SELECT CAST(AVG(grade) AS integer) as avg_grade,
SUM(CASE WHEN status = 'one' THEN 1 ELSE 0 END) as one,
SUM(CASE WHEN status = 'two' THEN 1 ELSE 0 END) as two,
SUM(CASE WHEN status = 'three' THEN 1 ELSE 0 END) as three
FROM A
WHERE status IN ('one', 'two', 'three');
The above queries give me expected output as :
| avg(grade) | one | two | three |
|---|---|---|---|
| 4 | 3 | 1 | 2 |
If you are fine with decimal values for column avg(grade)
You can limit those to two places as ROUND(AVG(grade), 2) as avg_grade,
In this case;
The query in mysql and postgresql would be same as below :
INSERT INTO B(avg_grade, one, two, three)
SELECT ROUND(AVG(grade), 2) as avg_grade,
SUM(CASE WHEN status = 'one' THEN 1 ELSE 0 END) as one,
SUM(CASE WHEN status = 'two' THEN 1 ELSE 0 END) as two,
SUM(CASE WHEN status = 'three' THEN 1 ELSE 0 END) as three
FROM A
WHERE status IN ('one', 'two', 'three');
This will return you the result as :
|avg(grade)| one | two | three |
| -------- | -------- | -------- |--------|
| 4.00 | 3 | 1 | 2 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论