如何可以顯示 student 如果 他們選讀 OPS400 和 HWD101. table name:grades column #1 :studentno column #2 :grade column #3 :course select studentno from grades where course='OPS400' and course='HWD101'; 但這個命令, 運行有問題... 理論上一個會出多個一個學生 因為一共有五個學生其中最少一個選讀 OPS400 & HWD101

Hi yesman,    可否對你的 tbale 再敘述清楚一點？    以下為我猜測你的 Table 架構所下的 SQL Command 你看看合不合用

select  distinct
        studentno 
from    grades d
where   exists (select * from grades t where t.studentno = d.studentno and course = 'OPS400')
and     exists (select * from grades t where t.studentno = d.studentno and course = 'HWD101')    或是    select  distinct
        studentno 
from    grades d
where   'OPS400' in (select course  from grades t where t.studentno = d.studentno)
and     'HWD101' in (select course  from grades t where t.studentno = d.studentno)

發表人 -

------
Fishman

謝 Fishman    我的table 如下    mysql> select studentno,course from grades order by studentno ASC; +-----------+--------+ | studentno | course | +-----------+--------+ |     22222 | OPS440 | |     22222 | OPS440 | |     22222 | OPS400 | |     43215 | OPS400 | |     43215 | hwd101 | |     43977 | DAT702 | |     43977 | DAT702 | |     43977 | OPS400 | |     55555 | Hwd101 | |     55555 | INT213 | |     55555 | OPS400 | |     97233 | NET401 | |     97233 | OPS440 | |     97233 | NET401 | |     97233 | hwd101 | |     97384 | INT213 | |     97384 | INT213 | |     97384 | NET401 | |     98987 | hwd101 | |     98987 | hwd101 | |     98987 | INT213 | +-----------+--------+ 21 rows in set (0.01 sec)         mysql> select studentno,course from grades where course='HWD101'; ----------- -------- | studentno | course | ----------- -------- | 55555 | Hwd101 | | 98987 | hwd101 | | 98987 | hwd101 | | 43215 | hwd101 | | 97233 | hwd101 | ----------- -------- 5 rows in set (0.00 sec) mysql> select studentno,course from grades where course='ops400'; ----------- -------- | studentno | course | ----------- -------- | 43215 | OPS400 | | 55555 | OPS400 | | 43977 | OPS400 | | 22222 | OPS400 | ----------- -------- 4 rows in set (0.00 sec) 理論上只會出兩個學生 同時選讀 OPS400 , HWD101 studentno : 55555 和 43215 但應該用什麼command 可以show 出來呢?

MySQL!!! < >< > 歹勢！我不會ㄝ！跟你說聲抱歉！ 請其他大大支援一下唄！ ---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman

我找到要好的朋友幫忙了.. 謝 正確答案如下: mysql> select studentno from grades where course='ops400' and studentno -> in(select studentno from grades where course='hwd101'); ----------- | studentno | ----------- | 43215 | | 55555 | ----------- 2 rows in set (0.00 sec)

另提供一種 試試看 有錯煩請指正

select studentno from grades where course in ('OPS400' ,HWD101')

========================= 讀萬卷書~不如來K.TOP走一遭 =========================

引言: 另提供一種 試試看 有錯煩請指正

select studentno from grades where course in ('OPS400' ,HWD101')

========================= 讀萬卷書~不如來K.TOP走一遭 =========================

這樣不對 select studentno from grades where course in ('OPS400' ,HWD101') 這一句的意思，跟 select studentno from grades where course='OPS400' or course='HWD101' 一樣 還有一種，用 self join： select a.studentno from grades a, grades b where a.studentno=b.studentno and a.course='HWD101' and b.course='ops400' 加油喔，喵～

select distinct studentno from (select studentno from grades where course='OPS400') t1, (select studentno from grades where course='HWD101') t2 where t1.studentno= t2.studentno