在WHERE子句中使用SELECT语句

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

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

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

Would this be a better alternative?

这会是一个更好的选择吗?

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

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...

它不那么优雅,但似乎比我以前的版本运行得更快。我不喜欢它,因为它在GUI中没有很清楚地显示(并且需要SQL初学者理解)。我可以将它分成两个单独的查询,但事情开始变得混乱......

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

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

7 个解决方案

#1

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

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

#2

It's not bad practice at all. They are usually referred as SUBQUERY, SUBSELECT or NESTED QUERY.

这根本不是一种做法。它们通常被称为SUBQUERY,SUBSELECT或NESTED QUERY。

It's a relatively expensive operation, but it's quite common to encounter a lot of subqueries when dealing with databases since it's the only way to perform certain kind of operations on data.

这是一个相对昂贵的操作,但在处理数据库时遇到很多子查询是很常见的,因为它是对数据执行某种操作的唯一方法。

#3

There's a much better way to achieve your desired result, using SQL Server's analytic (or windowing) functions.

使用SQL Server的分析(或窗口)功能,有一种更好的方法来实现您想要的结果。

SELECT DISTINCT Date, MAX(Score) OVER(PARTITION BY Date) FROM ScoresTable

If you need more than just the date and max score combinations, you can use ranking functions, eg:

如果您需要的不仅仅是日期和最大分数组合,您可以使用排名功能,例如:

SELECT  *
FROM    ScoresTable t
JOIN (   
    SELECT 
        ScoreId,
        ROW_NUMBER() OVER (PARTITION BY Date ORDER BY Score DESC) AS [Rank] 
        FROM ScoresTable
) window ON window.ScoreId = p.ScoreId AND window.[Rank] = 1

You may want to use RANK() instead of ROW_NUMBER() if you want multiple records to be returned if they both share the same MAX(Score).

如果您希望返回多个记录(如果它们共享相同的MAX(分数)),则可能需要使用RANK()而不是ROW_NUMBER()。

#4

The principle of subqueries is not at all bad, but I don't think that you should use it in your example. If I understand correctly you want to get the maximum score for each date. In this case you should use a GROUP BY.

子查询的原理并不坏,但我不认为你应该在你的例子中使用它。如果我理解正确,您希望获得每个日期的最高分。在这种情况下,您应该使用GROUP BY。

#5

This is a correlated sub-query.

这是一个相关的子查询。

(It is a "nested" query - this is very non-technical term though)

(这是一个“嵌套”查询 - 虽然这是非技术术语)

The inner query takes values from the outer-query (WHERE st.Date = ScoresTable.Date) thus it is evaluated once for each row in the outer query.

内部查询从外部查询(WHERE st.Date = ScoresTable.Date)获取值,因此对外部查询中的每一行计算一次。

There is also a non-correlated form in which the inner query is independent as as such is only executed once.

还存在一种非相关形式,其中内部查询是独立的,因此仅执行一次。

e.g.

 SELECT * FROM ScoresTable WHERE Score = 
   (SELECT MAX(Score) FROM Scores)

There is nothing wrong with using subqueries, except where they are not needed :)

使用子查询没有任何问题,除非不需要它们:)

Your statement may be rewritable as an aggregate function depending on what columns you require in your select statement.

您的语句可以作为聚合函数重写,具体取决于您在select语句中需要的列。

SELECT Max(score), Date FROM ScoresTable 
Group By Date

#6

In your case scenario, Why not use GROUP BY and HAVING clause instead of JOINING table to itself. You may also use other useful function. see this link

在您的情况下,为什么不使用GROUP BY和HAVING子句而不是JOINING表自身。您也可以使用其他有用的功能。看到这个链接

#7

Subquery is the name.

子查询是名称。

At times it's required, but good/bad depends on how it's applied.

有时它是必需的,但好/坏取决于它是如何应用的。

#1