我个人认为与嵌套的“表表达式”相比，CTE更容易阅读和调试，就像您所做的那样。我会做如下操作：

with
x as (
  select p.plant_name
  from plants p
  join weather w on w.plant_species = p.plant_species
  group by p.plant_name
  having count(*) = 1
)
select x.plant_name, w.weather_type
from x
join weather w on w.plant_species = x.plant_species

关于嵌套表表达式的可读性和调试的简易性，我不得不同意The Impaler的观点。作为CTE的另一个选择（这确实是更好的选择），如果你真的想嵌套东西而不想太多，你可以使用一个相关子查询。它更容易阅读，尽管随着结果集的增长，你会失去效率。

SELECT w.weather_type, p.plant_name 
FROM plants p
JOIN weather w 
    ON w.plant_species = p.plant_species
WHERE (SELECT COUNT(1) FROM dbo.weather WHERE plant_species = w.plant_species) = 1

或者分组...

SELECT w.weather_type, p.plant_name 
FROM plants p
JOIN weather w 
    ON w.plant_species = p.plant_species
WHERE w.plant_species IN (SELECT plant_species FROM dbo.weather  GROUP BY plant_species HAVING COUNT(1) = 1)

SELECT w.weather_type, p.plant_name 
FROM plants p
JOIN weather w 
ON w.plant_species = p.plant_species
WHERE w.weather_type="Sunny";