首先说下我这个不是作业题,已经想很久了,能查询出,但结果总是出错
四个表:ShiYanTable,ExamCreditTable ,CourseCreditTable ,School
共有字段:ID、SchoolID
表School还有一个字段:SchoolName
我想实现这样的结果:
依据SchoolID,从ShiYanTable,ExamCreditTable ,CourseCreditTable 表里查询出各个学院的记录数
例如,计算机学院的SchoolID是10,现从ShiYanTable,ExamCreditTable ,CourseCreditTable 三个表里分别查出SchoolID是10的记录数,然后列出如下表:
SchoolID SYT记录数 ECT记录数 CCT记录数
计算机学院 XXX XXXXX XXXXX我不太清楚这几个表里如何连接的,写了下面段查询语句但总是出错,
SELECT
SchoolName AS 学院名称,
COUNT(SYT.ID) as 记录数S,
COUNT(ECT.ID) as 记录数E,
COUNT(CCT.ID) as 记录数C
FROM
School S
left join ShiYanTable SYT on S.SchoolID=SYT.SchoolID
left join ExamCreditTable ECT on SYT.SchoolID=ECT.SchoolID
left join CourseCreditTable CCT on ECT.SchoolID=CCT.SchoolID
WHERE
S.SchoolID>0 AND S.SchoolID<30
GROUP BY
S.SchoolName
求高手帮忙指点!谢谢
四个表:ShiYanTable,ExamCreditTable ,CourseCreditTable ,School
共有字段:ID、SchoolID
表School还有一个字段:SchoolName
我想实现这样的结果:
依据SchoolID,从ShiYanTable,ExamCreditTable ,CourseCreditTable 表里查询出各个学院的记录数
例如,计算机学院的SchoolID是10,现从ShiYanTable,ExamCreditTable ,CourseCreditTable 三个表里分别查出SchoolID是10的记录数,然后列出如下表:
SchoolID SYT记录数 ECT记录数 CCT记录数
计算机学院 XXX XXXXX XXXXX我不太清楚这几个表里如何连接的,写了下面段查询语句但总是出错,
SELECT
SchoolName AS 学院名称,
COUNT(SYT.ID) as 记录数S,
COUNT(ECT.ID) as 记录数E,
COUNT(CCT.ID) as 记录数C
FROM
School S
left join ShiYanTable SYT on S.SchoolID=SYT.SchoolID
left join ExamCreditTable ECT on SYT.SchoolID=ECT.SchoolID
left join CourseCreditTable CCT on ECT.SchoolID=CCT.SchoolID
WHERE
S.SchoolID>0 AND S.SchoolID<30
GROUP BY
S.SchoolName
求高手帮忙指点!谢谢
解决方案 »
- 小工具 能生成多表级联查询SQL语句
- 关于创建数据库问题
- select * from ab1 表中有1000万条记录,慢怎么解决
- 集群系统装sql server2005 没有ssis服务的问题求教
- 小弟刚学SQL不久,遇到个问题,希望大家进来看下啊!
- 高手救命,求一sql语句,这种统计太不符合规格啦
- 如何解决sql占用cpu过长的问题
- 请教一个问题?这个字段为什么不能设置“不能为空”?
- 按两个条件分组查询,每组只显示5条记录,很麻烦,请高手们帮忙,谢谢大家。
- 我想设计设计一个表B,当输入id时,能从令一个表A读入name字段。(A有id,name这两个字段)
- 疑难SQL语句问题!请教!
- sql 如何用别名作为查询条件
SchoolName AS 学院名称,
(select COUNT(SYT.ID) from ShiYanTable where SchoolID=S.SchoolID ) as 记录数S,
……
FROM
School S
WHERE
S.SchoolID>0 AND S.SchoolID<30
先试试。lz应该先看看join以后的结果是什么?
SchoolName AS 学院名称,
(select COUNT(SYT.ID) from ShiYanTable SYT where SchoolID=S.SchoolID ) as 记录数S
FROM
School S
WHERE
S.SchoolID>0 AND S.SchoolID <30 GROUP BY
S.SchoolID,S.SchoolName
这样写可以正确查询出一个结果了,那么怎么植入另外两个表啊?
代码见下:
SELECT
SchoolName AS 学院名称,
(select COUNT(SYT.ID) from ShiYanTable SYT where SchoolID=S.SchoolID ) as 记录数S,
(select COUNT(ECT.ID) from ExamCreditTable ECT where SchoolID=S.SchoolID ) as 记录数E,
(select COUNT(CCT.ID) from CourseCreditTable CCT where SchoolID=S.SchoolID ) as 记录数C
FROM
School S
WHERE
S.SchoolID>0 AND S.SchoolID <30 GROUP BY
S.SchoolID,S.SchoolName
搞定了,谢谢~~~~~~~~~~~~~~~