mysql中except语句出错,在查询中使用except时出错

This query works:

mysql> SELECT s.sno FROM students s;

+------+

| sno |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+------+

10 rows in set (0.00 sec)

This query also works:

mysql> SELECT t.sno FROM take t WHERE t.cno = 'CS112';

+------+

| sno |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.00 sec)

BUT this query:

SELECT s.sno FROM students s

EXCEPT

SELECT t.sno FROM take t WHERE t.cno = 'CS112';

fails with the error:

mysql> SELECT s.sno FROM students s

-> EXCEPT

-> SELECT t.sno FROM take t WHERE t.cno = 'CS112';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n

ear 'EXCEPT

SELECT t.sno FROM take t WHERE t.cno = 'CS112'' at line 2

What am I doing wrong here?

解决方案

I don't believe MySQL supports EXCEPT syntax. Try using NOT IN or a LEFT JOIN:

SELECT s.sno

FROM students s

WHERE s.sno NOT IN

(

SELECT t.sno

FROM take t

WHERE t.cno = 'CS112'

);

OR

SELECT s.sno

FROM students s

LEFT JOIN take t ON s.sno = t.sno

WHERE IFNULL(t.cno, '') != 'CS112'

UPDATE

I mocked up your data as such and it correctly returns 5 through 10:

create temporary table temp_students (sno int)

insert into temp_students values (1)

insert into temp_students values (2)

insert into temp_students values (3)

insert into temp_students values (4)

insert into temp_students values (5)

insert into temp_students values (6)

insert into temp_students values (7)

insert into temp_students values (8)

insert into temp_students values (9)

insert into temp_students values (10)

create temporary table temp_take (sno int, cno varchar(50))

insert into temp_take values (1, 'CS112')

insert into temp_take values (2, 'CS112')

insert into temp_take values (3, 'CS112')

insert into temp_take values (4, 'CS112')

SELECT s.sno

FROM temp_students s

LEFT JOIN temp_take t ON s.sno = t.sno

WHERE IFNULL(t.cno, '') != 'CS112'