我是SQL新手，无法从数据库中选择

Question

>我有下表，其中包含学生详细信息和 gpa

|clg_id  | sgpa |  sem_type |
-----------------------------
|o170431 | 9.6  |  sem1     |
|o170431 | 9.8  |  sem2     |
|o170440 | 9.2  |  sem1     |
|o170440 | 8.6  |  sem2     |
|o170450 | 8.2  |  sem1     |
|o170450 | 9.1  |  sem2     |
----------------------------

我希望查询后的结果表如下所示

|clg_id  | sem1_sgpa |  sem2_sgpa|
---------------------------------
|o170431 |    9.6    |  9.8     |
|o170440 |    9.2    |  8.6     |
|o170450 |    8.2    |  9.1     |
--------------------------------

我应该如何编写SQL查询来获得上述结果？

Answer 1

这样的事情应该可以工作：

SELECT clg_id,
SUM(CASE WHEN sem_type="sem1" THEN sgpa END) AS "sem1",
SUM(CASE WHEN sem_type="sem2" THEN sgpa END) AS "sem2",
#for additional sem, if any
SUM(CASE WHEN sem_type="sem3" THEN sgpa END) AS "sem3",
SUM(CASE WHEN sem_type="sem4" THEN sgpa END) AS "sem4"
FROM your_table
GROUP BY clg_id;

演示小提琴在这里：https://www.db-fiddle.com/f/oHgnnz1w9n7TdRz5Ak16eY/1

Answer 2

SELECT
p1.clg_id as cig_id ,p1.sgpa as sem1_gpa,p2.sgpa as sem2_gpa 
from
(select sgpa,clg_id from student_details where  sem_type='sem1') as p1
inner join
(select sgpa,clg_id from student_details where  sem_type='sem2') as p2  
on p1.clg_id = p2.clg_id ;

Answer 3

您可以将CASE与SUM一起使用来获取输出。

CASE是从列中筛选特定值sem_type。

GROUP BYSUM是将所有值相加并产生唯一的结果。

select
clg_id,
sum(case when sem_type = 'sem1' then sgpa end) as sem1_sgpa,
sum(case when sem_type = 'sem2' then sgpa end) as sem2_sgpa
from
student_details
group by 
clg_id;

是的，如果有 sem3，那么我想要一个额外的列

编辑：可以通过使用预准备语句创建动态查询来实现。

所以基本上它将适用于n数量的 sem(s(，而不是手动更新查询。

select 
group_concat(concat("sum(case when sem_type = '",sem_type,"' then ",sgpa," end) as ",sem_type,"_sgpa")) 
into @dynamiccol
from (select sem_type from student_details group by sem_type) t;
set @dynamicquery = concat("select clg_id, ",@dynamiccol," from student_details group by clg_id");
prepare stmt from @dynamicquery;
execute stmt;
deallocate prepare stmt;