在WHERE子句中使用SELECT语句 [英] Using a SELECT statement within a WHERE clause

问题描述

SELECT * FROM ScoresTable WHERE Score = 
  (SELECT MAX(Score) FROM ScoresTable AS st WHERE st.Date = ScoresTable.Date)

在WHERE子句中是否有使用SELECT语句描述的名称?这是好/不好的做法吗?

Is there a name to describe using a SELECT statement within a WHERE clause? Is this good/bad practice?

这会是更好的选择吗?

SELECT ScoresTable.* 
FROM ScoresTable INNER JOIN 
  (SELECT Date, MAX(Score) AS MaxScore 
  FROM ScoresTable GROUP BY Date) SubQuery 
  ON ScoresTable.Date = SubQuery.Date 
  AND ScoresTable.Score = SubQuery.MaxScore

它远不那么优雅，但运行起来比以前的版本要快.我不喜欢它，因为它在GUI中没有非常清晰地显示(并且SQL初学者需要理解它).我可以将其分为两个独立的查询，但是随后事情变得混乱了……

It is far less elegant, but appears to run more quickly than my previous version. I dislike it because it is not displayed very clearly in the GUI (and it needs to be understood by SQL beginners). I could split it into two separate queries, but then things begin to get cluttered...

我不仅需要日期和分数(例如姓名)

N.B. I need more than just Date and Score (e.g. name)

推荐答案

它称为相关子查询.它有它的用途.

It's called correlated subquery. It has it's uses.

这篇关于在WHERE子句中使用SELECT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！