SQL语句，高分在线等待....

Question

        Name      FileName                    UserID   FileId  Score     Num     Optiones
         李斯 百草园到三味书屋(45人听课) 100 102 30 6 A
李斯 百草园到三味书屋(45人听课) 100 102 4 1 B
李斯 百草园到三味书屋(45人听课) 100 102 3 1 C
李斯 百草园到三味书屋(45人听课) 100 102 6 3 D
王蒙 五柳先生自传(语文)          102     104 5 1 A
王蒙 五柳先生自传(语文)   102 104 9 3 C
立国 百草园到三味书屋(45人听课) 100 108 5 1 A
立国 百草园到三味书屋(45人听课) 100 108 4 1 B
立国 百草园到三味书屋(45人听课) 100 108 3 1 C
立国 百草园到三味书屋(45人听课) 100 108 4 2 D需要的                                                
 Name      FileName                 UserID  FileId Score(sum)   A   B  C  D
李斯 百草园到三味书屋(45人听课) 100 102 43 6   1  1  3     
王蒙 五柳先生自传(语文)          102     104 14 1   0  3  0
立国 百草园到三味书屋(45人听课) 100 108 16 1   1  1  2
求SQL语句，

Answer 1

WITH test (NAME, FileName ,UserID ,FileId, Score, Num ,Optiones)
 AS (
 select '李斯',  '百草园到三味书屋(45人听课)', 100, 102, 30, 6, 'A'
 UNION ALL 
 select '李斯',  '百草园到三味书屋(45人听课)', 100, 102, 4, 1, 'B'
 UNION ALL 
 select '李斯',  '百草园到三味书屋(45人听课)', 100, 102, 3, 1, 'C'
 UNION ALL 
 select '李斯',  '百草园到三味书屋(45人听课)', 100, 102, 6, 3, 'D'
 UNION ALL 
 select '王蒙',  '五柳先生自传(语文)', 102, 104, 5, 1, 'A'
 UNION ALL 
 select '王蒙',  '五柳先生自传(语文)', 102, 104, 9, 3, 'C'
 UNION ALL 
 select '立国',  '百草园到三味书屋(45人听课)', 100, 108, 5, 1, 'A'
 UNION ALL 
 select '立国',  '百草园到三味书屋(45人听课)', 100, 108, 4, 1, 'B'
 UNION ALL 
 select '立国',  '百草园到三味书屋(45人听课)', 100, 108, 3, 1, 'C'
 UNION ALL 
 select '立国',  '百草园到三味书屋(45人听课)', 100, 108, 4, 2, 'D'
 )
 SELECT NAME, FileName ,UserID ,FileId,SUM(Score)AS 'scroe(SUM)',ISNULL(SUM(CASE WHEN Optiones='A' THEN num END),0) [A] ,
 ISNULL(SUM(CASE WHEN Optiones='b' THEN num END),0) [b] ,
 ISNULL(SUM(CASE WHEN Optiones='c' THEN num END),0) [c] ,
 ISNULL(SUM(CASE WHEN Optiones='d' THEN num END),0) [D] 
 FROM test 
 GROUP BY NAME, FileName ,UserID ,FileId
 
 /*
 NAME FileName                   UserID      FileId      scroe(SUM)  A           b           c           D
 ---- -------------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
 李斯   百草园到三味书屋(45人听课)            100         102         43          6           1           1           3
 立国   百草园到三味书屋(45人听课)            100         108         16          1           1           1           2
 王蒙   五柳先生自传(语文)                 102         104         14          1           0           3           0
 警告: 聚合或其他 SET 操作消除了 Null 值。
 
 (3 行受影响)
 */

Answer 2

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([Name] VARCHAR(4),[FileName] VARCHAR(26),[UserID] INT,[FileId] INT,[Score] INT,[Num] INT,[Optiones] VARCHAR(1))
INSERT [tb]
SELECT '李斯','百草园到三味书屋(45人听课)',100,102,30,6,'A' UNION ALL
SELECT '李斯','百草园到三味书屋(45人听课)',100,102,4,1,'B' UNION ALL
SELECT '李斯','百草园到三味书屋(45人听课)',100,102,3,1,'C' UNION ALL
SELECT '李斯','百草园到三味书屋(45人听课)',100,102,6,3,'D' UNION ALL
SELECT '王蒙','五柳先生自传(语文)',102,104,5,1,'A' UNION ALL
SELECT '王蒙','五柳先生自传(语文)',102,104,9,3,'C' UNION ALL
SELECT '立国','百草园到三味书屋(45人听课)',100,108,5,1,'A' UNION ALL
SELECT '立国','百草园到三味书屋(45人听课)',100,108,4,1,'B' UNION ALL
SELECT '立国','百草园到三味书屋(45人听课)',100,108,3,1,'C' UNION ALL
SELECT '立国','百草园到三味书屋(45人听课)',100,108,4,2,'D'
--------------开始查询--------------------------SELECT * FROM 
(
SELECT [Name],[FileName],[UserID],[FileId],[SUMScore],[Num],[Optiones] FROM [tb] AS t
CROSS APPLY(SELECT [SUMScore]=SUM([Score]) FROM tb WHERE [Name]=t.[Name] AND [FileName]=t.[FileName]) app
) 
t 
PIVOT (MAX([Num]) FOR [Optiones]IN ([A],[B],[C],[D])) piv
----------------结果----------------------------
/* 
Name FileName UserID FileId SUMScore A B C D
李斯 百草园到三味书屋(45人听课) 100 102 43 6 1 1 3
立国 百草园到三味书屋(45人听课) 100 108 16 1 1 1 2
王蒙 五柳先生自传(语文) 102 104 14 1 NULL 3 NULL
*/