SQL 查询学习

1、表数据如下
ID     stuid   status
1 100      1
3 200      1
4 2343    1
5 52      3
6 42      5
7 333      1

想得到下面结果
stuid                                总数
100,200,2343,333         4

首先创建试验表

1 with tablea as 
2 (
3 select 1 as id,100 as stuid,1 as status union all 
4 select 3 ,200,1 union all 
5 select 4 ,2343,1 union all 
6 select 5 ,52,3 union all 
7 select 6 ,42,5 union all
8  select 7 ,333,1 ) 

SQL 语句实现

SELECT stuff(Select ‘,‘ +convert(nvarchar(100),stuid) from tablea where status=1 order by stuid FOR XML PATH(‘‘)),1,1,‘‘)

AS stuid,count(*)  as 总数 from tablea as a where status=1