56,687
社区成员
发帖
与我相关
我的任务
分享
create table stu
(
ID int(10) not null auto_increment,
name varchar(30),
subject varchar(10),
score int(10),
primary key (ID)
);
create table sub
(
S_ID int(10) not null auto_increment,
subject varchar(20),
primary key (S_ID)
);
insert into stu values ('1','小强','1',90);
insert into stu values ('2','小美','1',88);
insert into stu values ('3','小贱','2',91);
insert into sub values ('1','语文');
insert into sub values ('2','数学');
insert into sub values ('3','体育');
select column_name from Information_schema.columns where table_Name = 'stu' order by column_name desc;
select *
from (
select *
from (
select 姓名,'语文成绩' as c,语文成绩 as s from 成绩表
union all
select 姓名,'数学成绩' as c,数学成绩 as s from 成绩表
union all
select 姓名,'体育成绩' as c,体育成绩 as s from 成绩表
)t
order by 姓名,s desc
) v
group by 姓名
select * from(
select * from(
select 姓名,语文成绩 as 成绩,'语文成绩' as 字段名 from tb
union all
select 姓名,数学成绩 as 成绩,'数学成绩' as 字段名 from tb
union all
select 姓名,体育成绩 as 成绩,'体育成绩' as 字段名 from tb
)data order by 姓名, 成绩 desc limit 99999999999
)d group by 姓名