C#题目

CREATE TABLE [dbo].[student_score](
	[username] [nvarchar](20) NULL,
	[kemu] [nvarchar](20) NULL,
	[score] [int] NULL
) ON [PRIMARY]

 

1.sql中行列转置

 

2.语句

 select  username,
 sum(case when kemu = '数学' then score else 0 end) as '数学',
 sum(case when kemu = '英语' then score else 0 end )as '英语',
 sum(case when kemu = '语文' then score else 0 end) as '语文'
 from student_score
 group by username 

 ----------------------逆操作-------------------------------------------

create table student_socre
(
id int identity(1,1),
xingming nvarchar(20),
yuwenscore int,
shuxuescore int,
yingyuscore int

)
insert into student_socre(xingming,yuwenscore,shuxuescore,yingyuscore) values('张三',80,90,70)
insert into student_socre(xingming,yuwenscore,shuxuescore,yingyuscore) values('李四',90,85,95)
insert into student_socre(xingming,yuwenscore,shuxuescore,yingyuscore) values('王五',88,75,90)
select * from student_socre

select xingming ,'语文' as '科目',yuwenscore as '成绩' from student_socre
union all select xingming, '数学' as '科目' ,shuxuescore as '成绩' from student_socre
union all select xingming ,'英语' as '科目',yingyuscore as '成绩' from student_socre

--------------------------删除重复的记录--------------------------------------------------------

create table Person
(
    id int identity(1,1) primary key,
    name nvarchar(20),
    age int 
)

insert into Person(name,age) values ('lizhch',28)
insert into Person(name,age) values ('lizhch1',22)
insert into Person(name,age) values ('lizhch2',21)
insert into Person(name,age) values ('lizhch1',298)
insert into Person(name,age) values ('lizhch4',22)
insert into Person(name,age) values ('lizhch5',298)

select * from Person

delete from Person where id in
(
select MAX(id)  from Person group by name 
having COUNT(name)>1
)