统计sql问题

[sysmenu]

现在有一张学生考核表，stuInfo(id,name,check,evaluate)分别是学生的id，姓名，考核项，考核内容；多个老师可以对同一个学生评价相同的内容；
如图：

现在想要的效果是对考核的内容进行统计分类，如图：

这本来是一个面试题，介于小弟数据库不太会，所以想请问各位大侠，在SqlServer中这个sql语句怎么写？
谢谢！

[唐诗三百首]

用动态行列转换应该可以实现.

[在路上_-]

试试这样行不

select name, check, count(1) 考核总数,
  sum(case evaluate when '优' then 1 else 0 end) 优,
  sum(case evaluate when '良' then 1 else 0 end) 良,
  sum(case evaluate when '差' then 1 else 0 end) 差
from stuInfo
group by id, name, check

[jiajiaren]

IF OBJECT_ID('Test') IS NOT NULL
DROP TABLE Test
GO
CREATE TABLE Test(id INT IDENTITY(1,1),NAME VARCHAR(6),[CHECK] VARCHAR(4),evaluate VARCHAR(2))
INSERT INTO Test SELECT '张三','学习','优'
UNION ALL SELECT '张三','学习','优'
UNION ALL SELECT '张三','纪律','优'
UNION ALL SELECT '张三','纪律','良'
UNION ALL SELECT '张三','学习','差'
UNION ALL SELECT '张三','学习','优'
UNION ALL SELECT '李四','纪律','优'
UNION ALL SELECT '李四','学习','优'
UNION ALL SELECT '李四','纪律','良'
UNION ALL SELECT '李四','学习','差'
UNION ALL SELECT '李四','纪律','优'
UNION ALL SELECT '李四','学习','良'


--------------------------------------------------动态查询------------------------------------------------------------
DECLARE @Sql NVARCHAR(4000)
SET @sql='
SELECT NAME ''姓名'',[CHECK] ''考核项'',COUNT(*) AS ''考核总数'''
SELECT @sql=@sql+',sum(CASE evaluate WHEN  '''+evaluate+''' THEN 1 ELSE 0 END)  '''+evaluate+''''
FROM  (SELECT DISTINCT evaluate FROM Test)a
SELECT @Sql=@Sql +' FROM dbo.Test 
GROUP BY  NAME,[CHECK] ORDER BY NAME DESC ,COUNT(*) DESC'
EXEC(@Sql)

---------------------------------------------------静态查询------------------------------------------------------------
SELECT NAME '姓名',[CHECK] '考核项',COUNT(*) AS '考核总数',sum(CASE WHEN evaluate='优' THEN 1 ELSE 0 END)  '优' 
,sum(CASE WHEN evaluate='良' THEN 1 ELSE 0 END)  '良',sum(CASE WHEN evaluate='差' THEN 1 ELSE 0 END)  '差'  FROM dbo.Test 
GROUP BY  NAME,[CHECK] ORDER BY NAME DESC ,COUNT(*) DESC 

---------------------------------------------------查询结果-------------------------------------------------------------
 /*
 姓名     考核项  考核总数        优           良           差
------ ---- ----------- ----------- ----------- -----------
张三     学习   4           3           0           1
张三     纪律   2           1           1           0
李四     学习   3           1           1           1
李四     纪律   3           2           1           0

 */

[shinger126]

就是这样的，无需动态

引用 2 楼 xxzxwsx 的回复:

试试这样行不

select name, check, count(1) 考核总数,
  sum(case evaluate when '优' then 1 else 0 end) 优,
  sum(case evaluate when '良' then 1 else 0 end) 良,
  sum(case evaluate when '差' then 1 else 0 end) 差
from stuInfo
group by id, name, check

[詩和遠方]

SELECT
	NAME,[Check],COUNT(*) AS 考核总数,
	SUM(CASE evaluate WHEN N'优' THEN 1 ELSE 0 END) AS 优,
	SUM(CASE evaluate WHEN N'良' THEN 1 ELSE 0 END) AS 良,
	SUM(CASE evaluate WHEN N'差' THEN 1 ELSE 0 END) AS 差
FROM #Test
GROUP BY NAME,[Check]
ORDER BY NAME

或

SELECT  NAME,[Check],优+良+差 AS 考核总数, 优,良,差
FROM    ( SELECT name,[Check],evaluate
          FROM  #Test
        ) a PIVOT (COUNT(evaluate) FOR evaluate IN (优,良,差)) b
ORDER BY NAME

NAME Check 考核总数 优 良 差 ------ ----- ----------- ----------- ----------- ----------- 李四 纪律 3 2 1 0 李四 学习 3 1 1 1 张三 纪律 2 1 1 0 张三 学习 4 3 0 1

[中国风]

或用Count过滤Null的特性处理 evaluate只存在3个值时没必要用动态

select name, [check], count(*) 考核总数,
  COUNT(case  when evaluate='优' then 1  end) 优,
  COUNT(case  when evaluate='良' then 1  end) 良,
  COUNT(case  when evaluate='差' then 1  end) 差
from stuInfo
group by  name, [check]

[极品老土豆]

if object_id('tempdb..#a','U') is not null  drop table #a
create table #a (id tinyint,
                   name varchar(8),
				   check1 varchar(8),
				   evaluate varchar(4)
				   )
insert into #a
        values(1,'张三','学习','优'),
(2,'张三','学习','优'),
(3,'张三','纪律','优'),
(4,'张三','纪律','良'),
(5,'张三','学习','差'),
(6,'张三','学习','优'),
(7,'李四','纪律','优'),
(8,'李四','学习','优'),
(9,'李四','纪律','良'),
(12,'李四','学习','差'),
(10,'李四','学习','优'),
(11,'李四','学习','良');
--能力展示语句
declare @sql varchar(5000)
select   @sql =isnull(@sql,' ')+',count(case when evaluate ='+quotename(evaluate,'''')+' then 1 else null end) as '+quotename(evaluate,'''')+'' from #a  group by evaluate
--print @sql
exec ('select name,check1 '+@sql+' from #a group by name,check1')
--结果展示
/*
name     check1   差           良           优
-------- -------- ----------- ----------- -----------
李四       纪律       0           1           1
张三       纪律       0           1           1
李四       学习       1           1           2
张三       学习       1           0           3
警告: 聚合或其他 SET 操作消除了 Null 值。

(4 行受影响)


*/
--精通展示
select * from #a
pivot(count(id) for evaluate in ([优],[良],[差])) as a
--结果展示
/*
name     check1   优           良           差
-------- -------- ----------- ----------- -----------
李四       纪律       1           1           0
张三       纪律       1           1           0
李四       学习       2           1           1
张三       学习       3           0           1

(4 行受影响)

*/

[lakerskobemvp]

create table stuInfo(id int,name varchar(10),checkcourse varchar(10),evaluate varchar(10)) insert into stuInfo select 1,'张三','学习','优' union all select 2,'张三','学习','优' union all select 3,'张三','纪律','优' union all select 4,'张三','纪律','差' union all select 5,'张三','学习','优' union all select 6,'张三','学习','差' union all select 7,'李四','学习','优' union all select 8,'李四','学习','良' union all select 9,'李四','纪律','差' select * from stuInfo select name,checkcourse,count(1)考核总数, sum(case evaluate when '优' then 1 else 0 end)优, sum(case evaluate when '良' then 1 else 0 end)良, sum(case evaluate when '差' then 1 else 0 end)差 from stuInfo group by name,checkcourse order by count(*) asc