USE [Test]
GO
/****** 对象: Table [dbo].[StudentT] 脚本日期: 07/27/2011 09:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[StudentT](
[Sid] [varchar](10) COLLATE Chinese_PRC_BIN NOT NULL,--学生ID
[Tid] [varchar](10) COLLATE Chinese_PRC_BIN NOT NULL,--老师ID
[Course] [varchar](10) COLLATE Chinese_PRC_BIN NOT NULL,--课程ID
[Score] [float] NOT NULL--分数ID
) ON [PRIMARY]GO
SET ANSI_PADDING OFFINSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('1','1','语文','55');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('1','2','数学','98');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('1','3','英语','61');INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('2','1','语文','91');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('2','2','数学','86');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('2','3','英语','92');INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('3','1','语文','89');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('3','2','数学','59');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('3','3','英语','92');显示结果应该为:
教师ID 课程ID 总人数 及格人数 及格率
1 语文 3 2 66.67%
2 数学 3 2 66.67%
3 英语 3 3 100%
GO
/****** 对象: Table [dbo].[StudentT] 脚本日期: 07/27/2011 09:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[StudentT](
[Sid] [varchar](10) COLLATE Chinese_PRC_BIN NOT NULL,--学生ID
[Tid] [varchar](10) COLLATE Chinese_PRC_BIN NOT NULL,--老师ID
[Course] [varchar](10) COLLATE Chinese_PRC_BIN NOT NULL,--课程ID
[Score] [float] NOT NULL--分数ID
) ON [PRIMARY]GO
SET ANSI_PADDING OFFINSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('1','1','语文','55');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('1','2','数学','98');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('1','3','英语','61');INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('2','1','语文','91');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('2','2','数学','86');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('2','3','英语','92');INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('3','1','语文','89');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('3','2','数学','59');
INSERT INTO [Test].[dbo].[StudentT]([Sid],[Tid],[Course],[Score])
VALUES('3','3','英语','92');显示结果应该为:
教师ID 课程ID 总人数 及格人数 及格率
1 语文 3 2 66.67%
2 数学 3 2 66.67%
3 英语 3 3 100%
select [Tid],[Course],(select count(distinct [Sid]) from [StudentT]) as s_sum,
sum(case when score>=60 then 1 else 0 end) as tc_sum,
ltrim(cast(sum(case when score>=60 then 1 else 0 end)*100./(select count(distinct [Sid]) from [StudentT]) as decimal(12,2))) + '%' JGL
from [StudentT]
group by [Tid],[Course]/**********Tid Course s_sum tc_sum JGL
---------- ---------- ----------- ----------- ------------------------------------------
2 数学 3 2 66.67%
3 英语 3 3 100.00%
1 语文 3 2 66.67%(3 行受影响)
列 tc_sum 应为:Tid 2 所教的学生人数考试及格的人数如果数据表数据不是整齐的三个老师对应三个学生的话。上面的SQL查出数据就不对。
SELECT A.教师ID,A.课程ID,A.总人数,A.及格数, ltrim(CAST(A.及格数*100.0/A.总人数 AS DECIMAL(5,2)))+'%' AS 及格率 FROM (
SELECT tid AS 教师ID,Course AS 课程ID,COUNT(DISTINCT sid) AS 总人数,(SELECT COUNT(DISTINCT sid) FROM StudentT AS b
WHERE b.Course=a.Course and b.Score>60 AND B.Tid=A.Tid) AS 及格数
FROM StudentT AS a
GROUP BY tid,Course) AS A
---------------------------------
2 数学 2 1 50.00%
4 数学 1 1 100.00%
3 英语 3 3 100.00%
1 语文 3 2 66.67%我将第二条数据的TID改为4后查询的结果
SELECT *
, [及格率]=CAST(CAST(CAST([及格人数] AS DECIMAL(10,2))/CAST([总人数] AS DECIMAL(10,2))*100 AS DECIMAL(10,2)) AS VARCHAR(10)) + '%'
FROM (
SELECT [教师ID]=TID, [课程ID]=COURSE
, [总人数] = (SELECT COUNT(SID) FROM [STUDENTT] WHERE COURSE=T.COURSE)
, [及格人数] = (SELECT COUNT(SID) FROM [STUDENTT] WHERE COURSE=T.COURSE AND SCORE>=60)
FROM [STUDENTT] T
) R执行结果:教师ID 课程ID 总人数 及格人数 及格率
-------------------------------------------
1 语文 3 2 66.67%
2 数学 3 2 66.67%
3 英语 3 3 100.00%
1 语文 3 2 66.67%
2 数学 3 2 66.67%
3 英语 3 3 100.00%
1 语文 3 2 66.67%
2 数学 3 2 66.67%
3 英语 3 3 100.00%
上面的SQL语句sum(case when score>=60 then 1 else 0 end) as tc_sum,直接改成下面的SQL语句COUNT(DISTINCT sid)就行啦。
SELECT *
, [及格率]=CAST(CAST(CAST([及格人数] AS DECIMAL(10,2))/CAST([总人数] AS DECIMAL(10,2))*100 AS DECIMAL(10,2)) AS VARCHAR(10)) + '%'
FROM (
SELECT [教师ID]=TID, [课程ID]=COURSE
, [总人数] = (SELECT COUNT(SID) FROM [STUDENTT] WHERE COURSE=T.COURSE)
, [及格人数] = (SELECT COUNT(SID) FROM [STUDENTT] WHERE COURSE=T.COURSE AND SCORE>=60)
FROM [STUDENTT] T
group by TID, COURSE
) R
执行结果:教师ID 课程ID 总人数 及格人数 及格率
-------------------------------------------
2 数学 3 2 66.67%
3 英语 3 3 100.00%
1 语文 3 2 66.67%