新增SQL語法 (2010/11/26)

SELECT A.CLASS_ID,CLASS_NAME,CLASS_TEACHER,DAY,ROOM FROM courses_new JOIN
(SELECT CLASS_ID FROM schedule WHERE S_ID = '496511126') A
WHERE A.CLASS_ID = courses_new.CLASS_ID;

==>使用"學號" 查詢該學生的課表(選修的課) 可在SELECT調整需要的欄位
    


SELECT CLASS_NAME FROM (SELECT A.CLASS_ID,CLASS_NAME,CLASS_TEACHER,DAY,ROOM,START_TIME,END_TIME FROM courses_new JOIN
(SELECT CLASS_ID FROM schedule WHERE S_ID = '496511126') A
WHERE A.CLASS_ID = courses_new.CLASS_ID ) B
WHERE DAY = DATE_FORMAT( NOW(), '%a' )) AND ( CURTIME() BETWEEN START_TIME AND END_TIME);

==>使用"現在時間(格式:xx:yy:zz)" 來查詢 此時此課該學生要上的課







SELECT CLASS_NAME FROM courses_new JOIN (SELECT CLASS_ID FROM open WHERE T_ID = 1) A
WHERE A.CLASS_ID = courses_new.CLASS_ID;

==>使用 老師的T_ID 來查詢 老師所有開的課程名單





SELECT A.S_ID, DEPARTMENT, STUDENT_NAME FROM student JOIN (SELECT * FROM schedule s WHERE CLASS_ID = 2) A
WHERE A.S_ID = student.S_ID;

==>使用 CLASS_ID 來查詢 修課同學名單



SELECT * FROM sign_new s WHERE S_ID = 496511126 AND ROOM = 'SF648' AND DATE_FORMAT(ENTER_TIME,'%Y-%m-%d-') = DATE_FORMAT(NOW(),'%Y-%m-%d-') AND CLASS_ID = 27;


==>判斷此學生 在今天(日期)與CLASS_ID與學號 去判斷是否已簽到過??




SELECT ENTER_TIME FROM sign_new s WHERE DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') = CURDATE() AND CLASS_ID = 27;

==>

SELECT DISTINCT DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') FROM sign_new WHERE CLASS_ID = 27 ORDER BY ENTER_TIME

==>回傳該堂課的(今天以前的)上課日期

SELECT DEPARTMENT, S_ID, STUDENT_NAME, DATE_FORMAT(ENTER_TIME,'%T'),IS_SIGN FROM sign_new WHERE CLASS_ID = 27 AND DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') = '2010-11-27' AND IS_SIGN = '已簽到' ORDER BY ENTER_TIME

==>回傳 該堂課該日期 的所有已簽到學生



SELECT * FROM (SELECT A.S_ID, DEPARTMENT, STUDENT_NAME FROM student JOIN (SELECT * FROM schedule s WHERE CLASS_ID = 2) A
WHERE A.S_ID = student.S_ID) B
WHERE B.STUDENT_NAME
NOT IN
(SELECT STUDENT_NAME FROM sign_new WHERE CLASS_ID = 2 AND DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') = '2010-11-27' AND IS_SIGN = '已簽到' ORDER BY ENTER_TIME);

==>該堂課該日期 未到名單    修課名單-已到名單



UPDATE sign_new SET IS_SIGN = '未到' WHERE CLASS_ID = 27 AND S_ID = 496511126 AND DATE_FORMAT(ENTER_TIME,'%Y-%m-%d') = '2010-11-27'