mysql - CTE 语法错误

Question

我正在尝试向以下内容编写一个 sql 查询(来自本网站 http://sqlzoo.net/1b.htm)

In which years was the Physics prize awarded but no Chemistry prize. (WARNING - this question is way too hard for this level, you will need to use sub queries or joins).

第一次尝试:

with c as
(select yr, subject
from nobel
where subject <> 'Chemistry')

select yr
from c
group by yr
having c.subject ='Physics'

但是我得到一个语法错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'with c as (select yr, subject from nobel where subject <> 'Chemistry' at line 1

怎么了？

第二次尝试:

select o.yr
from 
(select yr, subject
from nobel
where subject <> 'Chemistry') o
group by o.yr
having o.subject ='Physics'

但是我得到一个语法错误:

Unknown column 'o.subject' in 'having clause'

怎么了？

第三次尝试:我如何使用 JOIN 执行此操作？

Answer 1

您的第一个查询中的问题是 having 子句。您只能在此处对列使用聚合

所以这些会起作用

;with c as 
(select yr, subject from nobel where subject <> 'Chemistry')  
select yr,count(c.subject) from c where c.subject ='Physics'  group by yr 

;with c as 
(select yr, subject from nobel where subject <> 'Chemistry')  
select yr,count(c.subject) from c group by yr  having count(c.subject) =1

和第二个一样的问题

Having in T- SQL