可以考虑用pivot函数。。。
还可以用decode函数
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下:
if (条件==值1)
then
return(翻译值1)
elsif (条件==值2)
then
return(翻译值2)
......
elsif (条件==值n)
then
return(翻译值n)
else
return(缺省值)
end if
/*----------------------第一种-静态---------------------------------------------------*/
create table tb( 姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
/* ----------oracle----decode行转列-----------------*/
create table tb( 姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
/* ----------oracle----decode行转列-----------------*/
select 姓名, sum(decode(课程, '语文', nvl(分数, 0), 0)) '语文',
sum(decode(课程, '数学', nvl(分数, 0), 0)) '数学',
sum(decode(课程, '语文', nvl(分数, 0), 0)) '语文'
from tb
group by 姓名
/* ----------mysql----decode行转列-----------------*/
select 姓名, sum(case when 课程='语文' then 分数 else 0 end) as 语文 ,
sum(case when 课程='数学' then 分数 else 0 end) as 数学 ,
sum(case when 课程='物理' then 分数 else 0 end) as 物理 ,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
/*-------------------------第一种----动态--------------------------------------------*/
/*解释
SET @EE='';
SELECT @EE:=GROUP_CONCAT(@EE,'select count(*) from ','',concat(table_name,char(10)),'' SEPARATOR '') from t_data_table_config;
SET @str=CONCAT('select ' ,@EE,' from t_data_table_config');
PREPARE stmt from @str;
EXECUTE stmt;
*/
/*
说明:以上语句以t_data_table_config表中的table_name生成对应的类似
select count(*) from table_name 的多条语句。由于group_concat的连接长度受参数group_concat_max_len的影响。因此在未能全部查询出处理的参数时,通过
SHOW variables like 'group_concat_max_len%';
set group_concat_max_len=102400;
来修改其长度,以解决该问题。
*/
/*-------------------------第二种----静态--------------------------------------------*/
create table tb2(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb2 values('张三',74,83,93)
insert into tb2 values('李四',74,84,94)
select * from (
SELECT 姓名, '语文' AS 课程 , 语文 AS 分数 FROM tb2
UNION ALL
SELECT 姓名, '数学' AS 课程 , 数学 AS 分数 FROM tb2
UNION ALL
SELECT 姓名, '物理' AS 课程 , 物理 AS 分数 FROM tb2
) t
order by 姓名
select 姓名, sum(case when 课程='语文' then 分数 else 0 end) as 语文 ,
sum(case when 课程='数学' then 分数 else 0 end) as 数学 ,
sum(case when 课程='物理' then 分数 else 0 end) as 物理 ,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
/*-------------------------第一种----动态--------------------------------------------*/
/*解释
SET @EE='';
SELECT @EE:=GROUP_CONCAT(@EE,'select count(*) from ','',concat(table_name,char(10)),'' SEPARATOR '') from t_data_table_config;
SET @str=CONCAT('select ' ,@EE,' from t_data_table_config');
PREPARE stmt from @str;
EXECUTE stmt;
*/
/*
说明:以上语句以t_data_table_config表中的table_name生成对应的类似
select count(*) from table_name 的多条语句。由于group_concat的连接长度受参数group_concat_max_len的影响。因此在未能全部查询出处理的参数时,通过
SHOW variables like 'group_concat_max_len%';
set group_concat_max_len=102400;
来修改其长度,以解决该问题。
*/
/*-------------------------第二种----静态--------------------------------------------*/
create table tb2(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb2 values('张三',74,83,93)
insert into tb2 values('李四',74,84,94)
select * from (
SELECT 姓名, '语文' AS 课程 , 语文 AS 分数 FROM tb2
UNION ALL
SELECT 姓名, '数学' AS 课程 , 数学 AS 分数 FROM tb2
UNION ALL
SELECT 姓名, '物理' AS 课程 , 物理 AS 分数 FROM tb2
) t
order by 姓名
转:有一个SQL题在面试中出现的概率极高,最近有学生出去面试仍然会遇到这样的题目,在这里跟大家分享一下。
题目:数据库中有一张如下所示的表,表名为sales。
年 | 季度 | 销售量 |
---|---|---|
1991 | 1 | 11 |
1991 | 2 | 12 |
1991 | 3 | 13 |
1991 | 4 | 14 |
1992 | 1 | 21 |
1992 | 2 | 22 |
1992 | 3 | 23 |
1992 | 4 | 24 |
要求:写一个SQL语句查询出如下所示的结果。
年 | 一季度 | 二季度 | 三季度 | 四季度 |
---|---|---|---|---|
1991 | 11 | 12 | 13 | 14 |
1992 | 21 | 22 | 23 | 24 |
我给出的答案是这样的:
select 年,
sum(casewhen 季度=1then 销售量 else0end) as 一季度,
sum(casewhen 季度=2then 销售量 else0end) as 二季度,
sum(casewhen 季度=3then 销售量 else0end) as 三季度,
sum(casewhen 季度=4then 销售量 else0end) as 四季度
from sales groupby 年;