面试问题帮助相对基本的 JOIN 和子查询

Question

我被要求：

为仅在一种天气下开花的每种植物打印以下列序列。

1. WEATHER_TYPE
2. PLANT_NAME">

图式

• 植物(表名)

  • PLANT_NAME、字符串、植物的名称。这是主键。
  • PLANT_SPECIES，刺痛，植物的种类。
  • SEED_DATE，日期，播种的日期。

• 天气(表名)

  • PLANT_SPECIES，弦，植物的种类。
  • WEATHER_TYPE，字符串，植物开花的天气类型。

我编写了下面的脚本，并针对示例输入对其进行了测试，并获得了预期的结果。我不知道这是否被认为是"打印"结果。

寻求对我可能错过的理解。如何使此脚本"更高效"和/或"更好"和/或"更健壮"？

SELECT WEATHER.WEATHER_TYPE, a.PLANT_NAME
FROM (SELECT b.PLANT_NAME, b.PLANT_SPECIES
FROM (SELECT PLANTS.PLANT_NAME, PLANTS.PLANT_SPECIES, PLANTS.SEED_DATE, WEATHER.WEATHER_TYPE
FROM PLANTS JOIN WEATHER 
ON PLANTS.PLANT_SPECIES = WEATHER.PLANT_SPECIES) b
GROUP BY b.PLANT_NAME, b.PLANT_SPECIES
HAVING count(*) = 1) a JOIN WEATHER
ON a.PLANT_SPECIES = WEATHER.PLANT_SPECIES

我在SQL Server Management Studio窗口中获得了预期的结果，但不确定它是否是提问者正在寻找的"打印"结果。

Answer 1

我个人认为与嵌套的"表表达式"相比，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

Answer 2

我必须同意 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)

Answer 3

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";

Answer 4

以下是上面"The Impaler"发布的更正查询：

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