select 学号,count(签到)+ count(留言) as total from 学生 a,签到 b,留言 c on a.学号 = b.学号 and a.学号 = c.学号 group by 学号 order by total desc
-- 学生表A ,签到B,留言C UPDATE A SET A.total = B.total INNER JOIN ( SELECT B.studentnum,(B.count(*)+C.count(*)) AS total FROM B,C WHERE B.studentnum = C.studentnum) D ON A.studentnum = D.studentnum;
UPDATE A SET A.total = B.total INNER JOIN ( SELECT B.studentnum,(B.count(*)+C.count(*)) AS total FROM B,C WHERE B.studentnum = C.studentnum GROUP BY studentnum ) D ON A.studentnum = D.studentnum
谢谢你们的回复,按照1#(不知道怎么直接在1#回复)的运行,需要学生同时签到并且有留言才可以实现,可是有些学生是没有留言的。3#的我看不懂。我自己写的:select s.*,from stu s order by (select count(c.id) from check c where c.stuid = s.stuid)+(select count(l.id) from liuyan l where l.stuid = s.stuid) desc不知道可以吗?
total就不知道怎么写,所以就不要了
不用啊,没有留言的话,count出来会是0的,不影响。
select s.stuid, s.Nmae, (select count(*) from check where stuid = s.stuid) + (select count(*) from liuyan where stuid = s.stuid) from stu s order by 3 desc
select 学号,count(签到)+ count(留言) as total
from 学生 a,签到 b,留言 c
on a.学号 = b.学号
and a.学号 = c.学号
group by 学号
order by total desc
UPDATE A
SET A.total = B.total
INNER JOIN (
SELECT B.studentnum,(B.count(*)+C.count(*)) AS total
FROM B,C
WHERE B.studentnum = C.studentnum) D
ON A.studentnum = D.studentnum;
SET A.total = B.total
INNER JOIN (
SELECT B.studentnum,(B.count(*)+C.count(*)) AS total
FROM B,C
WHERE B.studentnum = C.studentnum
GROUP BY studentnum ) D
ON A.studentnum = D.studentnum
(select count(*) from check where stuid = s.stuid) + (select count(*) from liuyan where stuid = s.stuid)
from stu s
order by 3 desc