我有3个表:
member
表:
|--------|---------------------|------------------|
| id | f_name | l_name |
|--------|---------------------|------------------|
| 1 | matt | jim |
| 2 | david | joyce |
|--------|---------------------|------------------|
mentor
表:
|--------|---------------------|---------------|
| id | fname | lname |
|--------|---------------------|---------------|
| 1 | mack | pet |
| 2 | larry | oley |
| 3 | roza | masti |
|--------|---------------------|---------------|
class
表:
|--------|-----------|----------|
| id | classes | Capacity |
|--------|-----------|----------|
| 1 | Math | 50 |
| 2 | chemistry | 40 |
| 3 | Physics | 60 |
|--------|-----------|----------|
我使用这个sql代码在网格中进行数据处理:
select mentor.id,mentor.fname + ' ' + mentor.lname, class.classes, class.capacity, from mentor, class where mentor.id = class.id
现在我想在这样的网格中显示数据,以计算类成员的数量
|--------|--------|----------|----------|--------------------|
|mentorid| mentor | classes | Capacity |num of member class |
|--------|--------|----------|----------|--------------------|
| 1 | mack | Math | 50 | 10 |
| 2 | larry |chemistry | 40 | 12 |
| 3 | roza |Physics | 60 | 7 |
|--------|--------|----------|----------|--------------------|
数字10、12和7是从以下查询中获得的:Select count(*) as num of member class from member where class = "Math"
。但我不能在上面的查询中使用这个子查询来获得一个结果
问题是如何计算类成员的数量,并将其与其他信息一起显示在网格中?
应该注意的是,访问被用作数据库
正如我所说,您需要使用所需的数据连接子查询,并使用始终连接
select
mentor.id
,mentor.fname + ' ' + mentor.lname
, class.class
, class.capacity
, num_mem.[num of member class]
from mentor INNER JOIN class ON mentor.id = class.id
INNER JOIN (Select class,count(*) as [num of member class] from member GROUP BY class) num_mem ON class.class = num_mem.class