sql service (case when then else end ..... group by)
1.
原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读, 查询此表后的结果显式如下( 及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句
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
运行结果如下：
2.
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
时间 胜 负
2005-05-09 2 2
2005-05-10 1 2
SQL语句
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
运行结果如下：
