莫名其妙就想到了这个,结果查了很多关于一维表和二维表之间的转换,当然,用excel好解决,这个就不太清楚,就写下来
SELECT name as '姓名',
SUM(CASE WHEN a.`subject` = '语文' THEN result ELSE null END) as '成绩1',
SUM(CASE WHEN a.`subject` = '数学' THEN result ELSE null END) as '成绩2',
SUM(CASE WHEN a.`subject` = '英语' THEN result ELSE null END) as '成绩3'
FROM cj as a GROUP BY NAME;
SELECT name as '姓名',SUM(a.result) as '合计',
SUM(CASE WHEN a.`subject` = '语文' THEN result ELSE null END) as '成绩1',
SUM(CASE WHEN a.`subject` = '数学' THEN result ELSE null END) as '成绩2',
SUM(CASE WHEN a.`subject` = '英语' THEN result ELSE null END) as '成绩3'
FROM cj as a GROUP BY NAME;
其实有个链接也还好https://bbs.csdn.net/topics/290089305,但这个怪怪的。
我们来看一下别人怎么写的
--木有合计,在SQL Server下测试通过
if object_id('tempdb..#tx') is not null drop table #tx
go
create table #tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);
insert into #tx (id,c1,c2,c3) values (1 ,'A1','B1',9)
insert into #tx (id,c1,c2,c3) values (2 ,'A2','B1',7)
insert into #tx (id,c1,c2,c3) values (3 ,'A3','B1',4)
insert into #tx (id,c1,c2,c3) values (4 ,'A4','B1',2)
insert into #tx (id,c1,c2,c3) values (5 ,'A1','B2',2)
insert into #tx (id,c1,c2,c3) values (6 ,'A2','B2',9)
insert into #tx (id,c1,c2,c3) values (7 ,'A3','B2',8)
insert into #tx (id,c1,c2,c3) values (8 ,'A4','B2',5)
insert into #tx (id,c1,c2,c3) values (9 ,'A1','B3',1)
insert into #tx (id,c1,c2,c3) values (10 ,'A2','B3',8)
insert into #tx (id,c1,c2,c3) values (11 ,'A3','B3',8)
insert into #tx (id,c1,c2,c3) values (12 ,'A4','B3',6)
insert into #tx (id,c1,c2,c3) values (13 ,'A1','B4',8)
insert into #tx (id,c1,c2,c3) values (14 ,'A2','B4',2)
insert into #tx (id,c1,c2,c3) values (15 ,'A3','B4',6)
insert into #tx (id,c1,c2,c3) values (16 ,'A4','B4',9)
insert into #tx (id,c1,c2,c3) values (17 ,'A1','B4',3)
insert into #tx (id,c1,c2,c3) values (18 ,'A2','B4',5)
insert into #tx (id,c1,c2,c3) values (19 ,'A3','B4',2)
insert into #tx (id,c1,c2,c3) values (20 ,'A4','B4',5)
SELECT c1
,sum(CASE
WHEN c2='B1' THEN c3
ELSE 0
END) AS [b1]
,sum(CASE
WHEN c2='B2' THEN c3
ELSE 0
END) AS [b2]
,sum(CASE
WHEN c2='B3' THEN c3
ELSE 0
END) AS [b3]
,sum(CASE
WHEN c2='B4' THEN c3
ELSE 0
END) AS [b4]
FROM #tx
GROUP BY c1
--动态的话
if object_id('tempdb..#tx') is not null drop table #tx
go
create table #tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);
insert into #tx (id,c1,c2,c3) values (1 ,'A1','B1',9)
insert into #tx (id,c1,c2,c3) values (2 ,'A2','B1',7)
insert into #tx (id,c1,c2,c3) values (3 ,'A3','B1',4)
insert into #tx (id,c1,c2,c3) values (4 ,'A4','B1',2)
insert into #tx (id,c1,c2,c3) values (5 ,'A1','B2',2)
insert into #tx (id,c1,c2,c3) values (6 ,'A2','B2',9)
insert into #tx (id,c1,c2,c3) values (7 ,'A3','B2',8)
insert into #tx (id,c1,c2,c3) values (8 ,'A4','B2',5)
insert into #tx (id,c1,c2,c3) values (9 ,'A1','B3',1)
insert into #tx (id,c1,c2,c3) values (10 ,'A2','B3',8)
insert into #tx (id,c1,c2,c3) values (11 ,'A3','B3',8)
insert into #tx (id,c1,c2,c3) values (12 ,'A4','B3',6)
insert into #tx (id,c1,c2,c3) values (13 ,'A1','B4',8)
insert into #tx (id,c1,c2,c3) values (14 ,'A2','B4',2)
insert into #tx (id,c1,c2,c3) values (15 ,'A3','B4',6)
insert into #tx (id,c1,c2,c3) values (16 ,'A4','B4',9)
insert into #tx (id,c1,c2,c3) values (17 ,'A1','B4',3)
insert into #tx (id,c1,c2,c3) values (18 ,'A2','B4',5)
insert into #tx (id,c1,c2,c3) values (19 ,'A3','B4',2)
insert into #tx (id,c1,c2,c3) values (20 ,'A4','B4',5)
select * from #tx
declare @sql varchar(8000)
set @sql='select c1,'
select @sql=@sql+'sum(case when c2='''+cast(c2 as varchar(10))+''' then c3 else 0 end)['+cast(c2 as varchar(10))+'],'
from (select distinct c2 from #tx ) a
print(@sql)
set @sql=left(@sql,len(@sql)-1)+' from #tx group by c1'
exec(@sql)
if object_id('tempdb..#tx') is not null drop table #tx
/*
A1 9 2 1 11
A2 7 9 8 7
A3 4 8 8 8
A4 2 5 6 14
*/
这种写法我没遇见过,记录一下。
当然,https://blog.csdn.net/a437629292/article/details/50317909这个链接讲到了所有,但是感觉没有意义吧,完全是代码思维。