1 USE T4st
 2 
 3 GO
 4 IF(EXISTS(SELECT * FROM sysobjects WHERE name =‘courseTes‘))
 5 DROP TABLE courseTes
 6 GO
 7 
 8 CREATE TABLE courseTes
 9 (
10    courseid INT NOT NULL PRIMARY KEY IDENTITY,
11    coursename NVARCHAR(50) NOT NULL,
12    score INT NOT NULL
13 )
14 
15 INSERT dbo.courseTes
16         ( coursename, score )
17 VALUES  ( N‘java‘, -- coursename - nvarchar(50)
18           N‘70‘  -- score - int
19           )
20 INSERT dbo.courseTes
21         ( coursename, score )
22 VALUES  ( N‘oracle‘, -- coursename - nvarchar(50)
23           N‘90‘  -- score - int
24           )
25 INSERT dbo.courseTes
26         ( coursename, score )
27 VALUES  ( N‘xml‘, -- coursename - nvarchar(50)
28           N‘40‘  -- score - int
29           )
30 INSERT dbo.courseTes
31         ( coursename, score )
32 VALUES  ( N‘jsp‘, -- coursename - nvarchar(50)
33           N‘30‘  -- score - int
34           )
35 INSERT dbo.courseTes
36         ( coursename, score )
37 VALUES  ( N‘servlet‘, -- coursename - nvarchar(50)
38           N‘80‘  -- score - int
39           )
40 SELECT * FROM courseTes
41 
42 SELECT t.courseid,t.coursename,t.score,
43 (
44    CASE
45    WHEN t.score > 60 THEN ‘pass‘
46    ELSE ‘fail‘
47    END  
48 ) 
49 AS mark
50 FROM courseTes AS t

 1 USE T4st
 2 
 3 GO
 4 IF(EXISTS(SELECT * FROM sysobjects WHERE name =‘Score‘))
 5 DROP TABLE Score
 6 GO
 7 
 8 CREATE TABLE Score
 9 (
10    TimeId NVARCHAR(50) NOT NULL,
11    SuccessOrFail NVARCHAR(50) NOT NULL,
12 )
13 
14 INSERT Score
15        (TimeId,SuccessOrFail)
16 VALUES (
17         N‘2005-05-09‘,
18         N‘胜‘
19         )
20 INSERT Score
21        (TimeId,SuccessOrFail)
22 VALUES (
23         N‘2005-05-09‘,
24         N‘胜‘
25         )
26 INSERT Score
27        (TimeId,SuccessOrFail)
28 VALUES (
29         N‘2005-05-09‘,
30         N‘负‘
31         )
32 INSERT Score
33        (TimeId,SuccessOrFail)
34 VALUES (
35         N‘2005-05-09‘,
36         N‘负‘
37         )
38 INSERT Score
39        (TimeId,SuccessOrFail)
40 VALUES (
41         N‘2005-05-10‘,
42         N‘胜‘
43         )
44 INSERT Score
45        (TimeId,SuccessOrFail)
46 VALUES (
47         N‘2005-05-10‘,
48         N‘负‘
49         )
50 INSERT Score
51        (TimeId,SuccessOrFail)
52 VALUES (
53         N‘2005-05-10‘,
54         N‘负‘
55         )
56 
57 SELECT * FROM Score
58 
59 SELECT s.TimeId AS ‘时间‘,
60 SUM(
61   CASE
62   WHEN s.SuccessOrFail =‘胜‘ THEN 1
63   ELSE 0
64   END
65 ) AS ‘胜‘,
66 SUM(
67   CASE
68   WHEN s.SuccessOrFail =‘负‘ THEN 1
69   ELSE 0
70   END
71 ) AS ‘负‘
72 FROM Score AS s GROUP BY s.TimeId