在SQL中，如何在表格内的字段上进行汇总 [英] In SQL, how to aggregate on a field inside the table

问题描述

很抱歉，TITLE的标题不够具体.我将尝试解释: 我是SQL的新手.我在工作，并写了一个查询，该查询有9列，可从许多表中获取信息. 第9列是机器类型的名称，第3列是代表一台机器工作一个月的时间的值. 我需要添加第10列，该列将针对每种类型的计算机，该类型的最大值为3列. 假设有5台XR类型的计算机(表中有5行)，其时间(第3列)为1,2,3,4,5(以小时为单位).我需要在第10列中，计算机类型为XR的所有行的值都将为5，因为这是该类型计算机的最大值.

I'm sorry the TITLE is not so specific. I'll try to explain: I'm new to SQL. I'm at work and wrote a query which has 9 columns that takes information out of many tables. on the 9th column are names of types of machines, on the 3rd are value representing the time a machine worked in a month. I need to add a 10th column which will have for each type of machine, the maximum of 3rd columns for this type. lets say there are 5 machines of type XR (5 rows in the table) with times (3rd column) of 1,2,3,4,5 (in hours). I need that on the 10th column, all rows where type of machine is XR will have the value 5, as it is the maximum for this type of machine.

我该怎么做?

任何帮助将不胜感激！

推荐答案

在SQL Server，Oracle和PostgreSQL中:

SELECT  *, MAX(col3) OVER (PARTITION BY col9)
FROM    mytable

在MySQL中:

SELECT  mt.*, maxcol3
FROM    (
        SELECT  col9, MAX(col3) AS maxcol3
        FROM    mytable
        ) q
JOIN    mytable mt
ON      mt.col9 = q.col9

这篇关于在SQL中，如何在表格内的字段上进行汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！