求一个sql语句，比较简单。

Question

1.jobs表{id,name} //表示一个作业
2.submitjobs表(id,jobid,userid) jobid关联Jobs表的id //对应一个学生对某次作业的一次提交。
比如：
jobs:
1,作业1
2,作业2

sumbmitjobs:
1，1，user1
2, 1,user2
3, 1,user3

现在想获取jobs表里的信息：select * from jobs。同时获取每个作业当前有多少同学提交作业。
请问如何写？

Answer 1

select j.*, (select count(*) from submitjobs s where j.id=s.jobid) from jobs j

Answer 2

mysql 为例：select a.*,count(b.userid)sbtotal from jobs a left join sumbmitjobs b on b.jobid=a.id gourp by b.jobid order b.userid

oracle 差不多 只是不能那样分组需要先分组sumbmitjobs 统计好然后作为子查询

Answer 3

SELECT COUNT(DISTINCT column(jobName)) FROM jobs LEFT JOIN submitjobs ON jobs.jobName=submitjobs.jobName

Answer 4

select b.name, a.cnt from 
jobs b left join (select jobid, count(userid) as cnt
from submitjobs
group by jobid) a
on b.id = a.jobid

Answer 5

没测试...

SELECT j.id, j.name, COUNT(sj.jobid) AS count
FROM `submitjobs`
AS sj
JOIN `jobs`
AS j
WHERE sj.jobid = j.id
GROUP BY sj.jobid

Answer 6

其实最简单

 select 想要的字段 from 表1,表2 ...表N  

此种写法不管有多少表都能连起来