sql 暂时存储

go
if(object_id('groupbytest1') is not null) drop table groupbytest1;
go
create table groupbytest1( name varchar(10),score int default(0));
insert  into groupbytest1 (name,score)
 select 'aaa',11
 union select 'aaa',19
  union select 'bbb', 12
union select 'bbb',18
union select 'ccc',19
union select 'ddd', 21
go
select * from groupbytest1;
select  name ,sum(score) as totalscore from groupbytest1 group by name
select  name ,sum(score) as totalscore from groupbytest1 group by name having sum(score) ='30'
情景二：
 姓名 科目 分数
 张三 语文 30
张三 数学 50
张三 英语 70
李四 语文 50
李四 数学 80
李四 英语 90

期望查询结果：

 姓名 语文 数学 英语
 张三 30 50 70
李四 50 80 90
go
if(object_id('exam') is not null ) drop table exam;
go
create table exam(name varchar(10),ttype varchar(20) ,scroe int default(0));
insert into exam (name,ttype,scroe) select '张三','语文',30
union select '张三','数学',50
union select '张三','英语',70
union select '李四','语文',50
union select '李四','数学',80
union select '李四','英语',90
go
select * from exam;
go

select name as '姓名',
max(case ttype when '数学'  then  scroe  else 0 end) as '数学',
max(case ttype when '语文'  then scroe  else 0 end ) as '语文',
max(case ttype when '英语'  then scroe else 0 end ) as '英语'
from exam group by name
go
//查询以班级科目分组求平均分
select ttype as 科目,avg(scroe) as 平均分 from exam group by ttype
//查询以学生分组求学生的平均分
select  name as 姓名,avg(scroe) as  个人平均分 from exam group by name

SELECT * FROM ( SELECT DISTINCT name FROM exam
 )A
 OUTER APPLY(
 SELECT ttype= STUFF(REPLACE(
 REPLACE(
 (
 SELECT ttype FROM exam N
 WHERE name = A.name
 FOR XML AUTO
 ), '<N ttype="', '/ ')
 , '"/>', ''), 1, 1, '')
 )B
OUTER APPLY(
 SELECT scroe= stuff(
 REPLACE(
 REPLACE(
 (
 SELECT scroe FROM exam N
 WHERE name = A.name
 FOR XML AUTO
 ), '<N scroe="', '/ ')
 , '"/>', ''),1,1,'')
 
 )C
 
  REPLACE( (SELECT scroe FROM exam N  where name='张三'
 FOR XML AUTO),'<N scroe="',' /')

go
if(object_id('pagertest') is not null ) drop table pagertest
go
create table pagertest(id int primary key identity,flag int default(0),class varchar(10))
go
insert into pagertest (flag,class)
 select 1,11
 union all
 select 2,19
 union all
 select 3,12
 union all
 select 1,18
 union all
 select 2,19
 union all
 select 3,21
 union all
 select 1,11
 union all
 select 2,19
 union all
 select 3,12
 union all
 select 1,18
 union all
 select 2,19
 union all
 select 3,21
go
select * from pagertest
select top 4 * from pagertest where id not in (select top 6 id from pagertest order by id )
select top 4 * from pagertest where id >
(select  max(id) from (select top 6 id from pagertest order by id ) as a )

select distinct  class,
(select sum(id) from pagertest where pagertest.class=t.class )as flag1
from pagertest   t  group  by class

select * from pagertest where id = (select max(id) from pagertest)