关于sql查询要求 [英] Regarding sql query requirement
问题描述
全部好,
我有三张桌子
1.组织表
2.班级表
2.学生表
组织表中的栏目是
Id ,orgname,orgAddress
班级表中的栏目是
Id,Orgid,Classname
注意(orgtable的Id列是类表中Orgid的fk)
学生表中的列是
Id,ClassId,StudentName,Address,PhoneNo
注意(班级表的Id列是学生表中ClassId的fk)
所以我的要求是我需要完整的组织细节以及每个组织的学生人数
输出:
Slno,orgname,orgAddress,学生人数
1 pvrInstitute Banglore 30
2 nskInstitute naagpur 50
我尝试过:
HI全部,
我有三张桌子
1.组织表
2.班级表
2.学生表
组织表中的栏目是
Id,orgname,orgAddress
班级表中的栏目是
Id,Orgid,Classname
注意(orgtable的Id列是类表中Orgid的fk)
学生表中的列是
Id,ClassId,StudentName,Address,PhoneNo
注意(类表的Id列是学生表中ClassId的fk)
所以我的要求是我需要完整的组织细节以及每个组织的学生人数
输出:
Slno,orgname,orgAddress,学生人数
1 pvrInstitute Banglore 30
2 nskInstitute naagpur 50
使用JOIN: SQL联接 [ ^ ]
这是你的作业,所以我不会给你代码。只需阅读文章,看看它是如何工作的 - 它并不复杂,所以按照yoru DB的例子,你应该没事。
我得到了解决方案
SELECT o.Id,o.OrganisationCode,o.OrganisationName,count(*)As StudentCount
FROM Students S
INNER JOIN Classes C on s.ClassId = C.Id
INNER JOIN组织O on c.OrganisationId = o.Id
GROUP BY o.Id,o.OrganisationCode,o.OrganisationName
HI all,
I have three tables
1. Organisation table
2. Class table
2. Student Table
Columns in organisation table are
Id, orgname, orgAddress
Columns in class table are
Id, Orgid,Classname
Note(Id column of orgtable is fk for Orgid in class table)
Columns in student table are
Id,ClassId,StudentName,Address,PhoneNo
Note(Id column of class table is fk for ClassId in student table)
So my requirement is i need complete organisation details along with student count for each organisation
Output:
Slno, orgname, orgAddress, student count
1 pvrInstitute Banglore 30
2 nskInstitute naagpur 50
What I have tried:
HI all,
I have three tables
1. Organisation table
2. Class table
2. Student Table
Columns in organisation table are
Id, orgname, orgAddress
Columns in class table are
Id, Orgid,Classname
Note(Id column of orgtable is fk for Orgid in class table)
Columns in student table are
Id,ClassId,StudentName,Address,PhoneNo
Note(Id column of class table is fk for ClassId in student table)
So my requirement is i need complete organisation details along with student count for each organisation
Output:
Slno, orgname, orgAddress, student count
1 pvrInstitute Banglore 30
2 nskInstitute naagpur 50
Use a JOIN: SQL Joins[^]
This is your homework, so I'll not give you the code. Just read the article, and see how it works - it's not complex, so follow the example with yoru DB and you should be fine.
I got the solution
SELECT o.Id, o.OrganisationCode, o.OrganisationName, count(*) As StudentCount FROM Students S INNER JOIN Classes C on s.ClassId = C.Id INNER JOIN Organisations O on c.OrganisationId = o.Id GROUP BY o.Id, o.OrganisationCode, o.OrganisationName
这篇关于关于sql查询要求的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!