用SQL查询任课老师，谢谢了

ubound · 发表于 2012-7-12 14:53

用SQL查询：
1、查询一人教一门以上课的老师
2、查询同时教一个班级以上的老师
3、查询任课最多的老师

谢谢了!

flowergod · 发表于 2012-7-12 15:23

为啥要用SQL不直接用公式？

ubound · 发表于 2012-7-12 17:19

哈哈，俺对sql有点喜欢但水平低，想通过老师指导早日入门。{:soso_e100:}

ubound · 发表于 2012-7-12 23:23

顶一下{:soso_e100:}

zhaogang1960 · 发表于 2012-7-12 23:41

本帖最后由 zhaogang1960 于 2012-7-12 23:43 编辑

1、查询一人教一门以上课的老师

SQL = "select a.老师 from (select 语文老师 as 老师 from [Sheet1$]) as a,(select 数学老师 as 老师 from [Sheet1$]) as b where a.老师=b.老师 union select a.老师 from (select 语文老师 as 老师 from [Sheet1$]) as a,(select 英语老师 as 老师 from [Sheet1$]) as c where a.老师=c.老师 union select b.老师 from (select 数学老师 as 老师 from [Sheet1$]) as b,(select 英语老师 as 老师 from [Sheet1$]) as c where b.老师=c.老师"

复制代码

ubound · 发表于 2012-7-13 00:41

zhaogang1960 版主辛苦啦{:soso_e160:}

wuxiang_123 · 发表于 2012-7-13 09:23

本帖最后由 wuxiang_123 于 2012-7-13 11:45 编辑

1、查询一人教一门以上课的老师：

SELECT 老师 FROM
(SELECT 语文老师 AS 老师,'语文' FROM [Sheet1$]
UNION
SELECT 数学老师,'数学' FROM [Sheet1$]
UNION
SELECT 英语老师,'英语' FROM [Sheet1$])
GROUP BY 老师
HAVING COUNT(老师)>=2

复制代码

2、查询同时教一个班级以上的老师

SELECT 老师 FROM
(SELECT 语文老师 AS 老师,班级 FROM [Sheet1$]
UNION
SELECT 数学老师,班级 FROM [Sheet1$]
UNION
SELECT 英语老师,班级 FROM [Sheet1$])
GROUP BY 老师
HAVING COUNT(老师)>=2

复制代码

3：查询任课最多的老师(这句有歧义，我的理解是相同科目只算一科，如在不同班级担任语文科老师，只算是担任一科）

SELECT TOP 1 老师,count(老师) AS 科数 FROM
(SELECT 语文老师 AS 老师,'语文' AS 科目 FROM [Sheet1$]
UNION
SELECT 数学老师,'数学' FROM [Sheet1$]
UNION
SELECT 英语老师,'英语' FROM [Sheet1$])
GROUP BY 老师
ORDER BY 2 DESC

复制代码

如果不要返回科数，可以这样：

SELECT TOP 1 老师 FROM

(SELECT 语文老师 AS 老师,'语文' AS 科目 FROM [Sheet1$]

UNION

SELECT 数学老师,'数学' FROM [Sheet1$]

UNION

SELECT 英语老师,'英语' FROM [Sheet1$])

GROUP BY 老师

ORDER BY count(老师) DESC
复制代码
如果第3问指的是返回任课次数最多的老师，可以这样：

SELECT TOP 1 老师 FROM

(SELECT 语文老师 AS 老师 FROM [Sheet1$]

UNION ALL

SELECT 数学老师 FROM [Sheet1$]

UNION ALL

SELECT 英语老师 FROM [Sheet1$])

GROUP BY 老师

ORDER BY count(老师) DESC

复制代码

zhaogang1960 · 发表于 2012-7-13 10:36

wuxiang_123 发表于 2012-7-13 09:23
1、查询一人教一门以上课的老师：
或

吴版主，一门以上应该不包含1本身吧，经测试
SELECT 老师 FROM
(SELECT 语文老师 AS 老师 FROM [Sheet1$]
UNION
SELECT 数学老师 FROM [Sheet1$]
UNION
SELECT 英语老师 FROM [Sheet1$])
WHERE 老师
只是去重复，没有过滤掉只有一门课的

wuxiang_123 · 发表于 2012-7-13 11:35

zhaogang1960 发表于 2012-7-13 10:36
吴版主，一门以上应该不包含1本身吧，经测试
SELECT 老师 FROM
(SELECT 语文老师 AS 老师 FROM [Sheet1 ...

如果是不包含一门的话，那就这样。

SELECT 老师 FROM
(SELECT 语文老师 AS 老师,'语文' FROM [Sheet1$]
UNION
SELECT 数学老师,'数学' FROM [Sheet1$]
UNION
SELECT 英语老师,'英语' FROM [Sheet1$])
GROUP BY 老师
HAVING COUNT(老师)>=2

复制代码

zhaogang1960 · 发表于 2012-7-13 11:38

wuxiang_123 发表于 2012-7-13 11:35
如果是不包含一门的话，那就这样。

谢谢吴版主

		自动登录	找回密码
密码			免费注册

[求助] 用SQL查询任课老师，谢谢了

最新热点 /1