非数字列中的SQL MAX函数 [英] SQL MAX function in non-numeric columns

问题描述

据我了解MAX函数，它将返回给定列的最大值.对于数字值(例如，薪水列)，我很清楚-这是我在教程中找到的唯一应用程序.但是，我有一个问题要弄清楚在非数字列的情况下它是如何工作的.

As far as I understand the MAX function, it shall return a maximum value from a given column. In case of numeric values, for example a salary column, it is clear for me - and this is the only application I find in tutorials. However, I have a problem to understand how does it work in case of non-numeric columns.

我的问题源自此练习(在sql-ex.ru上)

My problems originates from this exercise (on sql-ex.ru)

查找仅生产相同型号的制造商，并且这些型号的数量超过1. 表格产品"包括有关制造商，型号和类型("PC"，笔记本电脑"或打印机")的信息. 解决方案之一是:

Find out makers who produce only the models of the same type, and the number of those models exceeds 1. The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). One of the solutions to this is:

SELECT maker,
       MAX(type) AS type
FROM   product
GROUP  BY maker
HAVING COUNT(DISTINCT type) = 1
       AND COUNT(model) > 1 

我不了解max的功能-它算什么?我尝试了一个更简单的查询来理解它，但这只会使它变得更加困难.

I don't understand the function of max - what does it count? I tried a simpler query to understand it, but it only made it more difficult.

SELECT maker,
       MAX(type) AS type, COUNT(type) AS QTY
FROM product
GROUP BY maker
ORDER BY maker

返回的集合是

maker  type      QTY
A      Printer   7
B      PC        2
C      Laptop    1
D      Printer   2
E      Printer   4

在我看来，MAX(type)似乎显示一个随机值，例如为什么对于制造商B，结果是PC，而不是笔记本电脑?为什么对于E是打印机而不是PC?

The MAX(type) seems to me to show a random value e.g. why for the maker B the result is PC and not Laptop? Why for E it is Printer and not PC?

全表

推荐答案

函数MAX，MIN等在应用于文本列时使用字典顺序.因此，您的MAX(type)将返回"Printer"而不是"PC"，因为"Printer"按字母顺序位于(大于)"PC"之后.

The functions MAX, MIN, etc. use the lexicographic order when applied to text columns. Therefore, your MAX(type) will return 'Printer' instead of 'PC' because 'Printer' is after (greater than) 'PC' in alphabetic order.

请注意，在您的第一个查询中，条件HAVING COUNT(distinct type) = 1表示每个组只能有一个type值.之所以使用select中的MAX(type)子句，是因为type不能直接在选择中使用，因为它不在GROUP BY子句中.

Notice that in your first query the condition HAVING COUNT(distinct type) = 1 means that there can only be a single typevalue for each group. The MAX(type) clause in the select is used because simply type can not be used in the select as it is not in the GROUP BY clause.

这篇关于非数字列中的SQL MAX函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！