2021-05-17

1.SELECT xh,xm,xb,bjmc FROM bjb,xsb WHERE xsb.bjb_id=bjb.id;
2.SELECT xh,xm,xb,bjmc,yw,sx FROM bjb,xsb,cjb WHERE bjb.id=xsb.bjb_id AND xsb.id=cjb.id;
3.SELECT bjb_id,bjmc,COUNt(bjb_id) FROM bjb,xsb WHERE xsb.bjb_id=bjb.id GROUP BY bjb_id;
4.SELECT xh,xm,bjmc,yw+sx FROM bjb,xsb,cjb WHERE xsb.bjb_id=bjb.id AND cjb.id=xsb.id AND yw+sx>=150 GROUP BY xsb.id;
5.SELECT bjb.id,bjmc,AVG(yw),AVG(sx) FROM bjb,xsb,cjb WHERE bjb.id=xsb.bjb_id AND xsb.id=cjb.id GROUP BY bjb.id;
6.SELECT xh,xm,yw,sx FROM xsb,cjb WHERE xsb.id=cjb.id AND yw>=60 AND sx>=60 GROUP BY xsb.id;
7.SELECT xh,xm,yw,sx FROM xsb,cjb WHERE xsb.id=cjb.id AND (yw<60 or sx<60) GROUP BY xsb.id;
8.SELECT a.id,bjmc,COUNT(bjb_id) rensxu
FROM bjb a RIGHT JOIN xsb b
ON a.id=b.bjb_id
GROUP BY bjb_id
HAVING rensxu>=2;
9.SELECT bjb_id,bjmc,COUNt(bjb_id) FROM bjb,xsb WHERE xsb.bjb_id=bjb.id GROUP BY bjb_id HAVING COUNT(bjb_id)>=2;
这行注释掉同上# 9. SELECT xh,xm,xb,bjmc FROM xsb,cjb,bjb WHERE bjb.id=xsb.bjb_id AND xsb.id NOT IN (SELECT id FROM cjb) GROUP BY xh;
10. SELECT xh,xm,
if( yw>=60 ,‘合格’,‘不合格’)语文合格情况,
if( sx>=60 ,‘合格’,‘不合格’)数学合格情况
FROM xsb LEFT JOIN cjb
ON xsb.id=cjb.id