SQLserver分组查询

if  exists (select * from sysobjects where name='testSum') 
drop table testSum 


---<4>创建表 
create table testSum 
( 
tid int primary key identity(1,1), 
tname varchar(30) null, 
tscor int null 
) 


insert into testSum (tname,tscor) 
select 'aaa',11 
union all 
select 'aaa',19 
union all 
select 'bbb',12 
union all 
select 'bbb',18 
union all 
select 'ccc',19 
union all 
select 'ddd',21 






---查询语句
select * from testSum 
select tname ,sum(tscor) from testSum group by tname 
---只查询tscor总和为30的 

select tname ,sum(tscor) from testSum group by tname having sum(tscor)=30 

if exists(select * from sysobjects where name='testScore') 
drop table testScore 


---创建表 
create table testScore 
( 
tid int primary key identity(1,1), 
tname varchar(30) null, 
ttype varchar(10) null, 
tscor int null 
) 


---插入数据 
insert into testScore values ('张三','语文',90) 
insert into testScore values ('张三','数学',20) 
insert into testScore values ('张三','英语',50) 
insert into testScore values ('李四','语文',30) 
insert into testScore values ('李四','数学',47) 
insert into testScore values ('李四','英语',78) 
---查询 


select*   from   testScore




select tname as '姓名' , 
max(case ttype when '语文' then tscor else 0 end) '语文', 
max(case ttype when '数学' then tscor else 0 end) '数学', 
max(case ttype when '英语' then tscor else 0 end) '英语' 
from testScore 
group by tname 














if exists(select * from sysobjects where name='#tmp') 
drop table #tmp 


create table #tmp(rq varchar(10),shengfu nchar(1)) 
 
insert into #tmp values('2005-05-09','胜') 
insert into #tmp values('2005-05-09','胜') 
insert into #tmp values('2005-05-09','负') 
insert into #tmp values('2005-05-09','负') 
insert into #tmp values('2005-05-10','胜') 
insert into #tmp values('2005-05-10','负') 
insert into #tmp values('2005-05-10','负') 


select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq 




select N.rq,N.勝,M.負 from ( 
select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join 
(select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq 

create table table2(id int , value varchar(10))
insert into table2 
select 1,'a' union all
select 1,'b' union all
select 2,'x' union all
select 2,'y' union all
select 2,'z' 


SELECT id,
(SELECT value + '' FROM table2 WHERE id=a.id FOR XML PATH('')) AS [values]
FROM table2 AS a GROUP BY a.id






select distinct a.id,
(select b.value+'' from table2 b where b.id=a.id for XML path('')) as value
from table2 a