仅使用SQL中的MAX函数更新重复的行

问题描述

我有一个像这样的表，其中，出于示例的原因，假设NAME是唯一标识符.

I have a table like this, where, suppose for the sake of an example, NAME is a unique identifier.

NAME    AGE         VALUE
Jack    Under 65    3
Jack    66-74       5
John    66-74       7
John    Over 75     9
Gill    25-35       11

某些NAME具有多个AGE，这是不希望的，因为这是由于数据的肮脏程度所致.

Some NAMEs have more than one AGE, which is undesirable, as this is due to dirtiness of the data.

我的目标是仅将重复副本更新为每个NAME中都有一个AGE.因此，所需的输出为:

My aim is to update the duplicates only to have one AGE within each NAME. The desired output is thus:

NAME    AGE         VALUE
Jack    Under 65    3
Jack    Under 65    5
John    66-74       7
John    66-74       9
Gill    25-35       11

类似UPDATE语句的东西应该可以，但是不能.

Something like this UPDATE statement should work, but it doesn't.

UPDATE table t1
SET t1.age=MAX(t1.age)
WHERE EXISTS (SELECT COUNT(t2.AGE)
              FROM table t2
              WHERE t1.NAME=t2.NAME
              GROUP BY t2.NAME
              HAVING COUNT(t2.AGE) > 1)

SQL Error: ORA-00934: group function is not allowed here

第二个问题

即使我使上面的陈述起作用，也存在第二个问题.此处的想法是对字符串使用MAX(或MIN)函数为组内的所有重复设置相同的值.

Even if I got the above statement to work, there is a second issue. The idea there is to use the MAX (or MIN) function on strings to set the same value for all repeats within a group.

但是不幸的是，这也无法按预期工作.为了保持一致性，理想情况下，年龄应默认为最低年龄组.但是，由于MAX/MIN比较字符串的字母顺序，因此，例如:

But unfortunately, this too would not quite work as desired. For consistency, ideally an age would default to the lowest age group. But because MAX/MIN compare alphabetic order on strings, this would give, e.g.:

• "66-74"和"65岁以下" => MAX ="65岁以下"-最低
• "66-74"和"75岁以上" => MAX ="75岁以上"-最高

只有四个年龄段，可以指定自定义订单吗?

There are only four age groups, would it be possible to specify a custom order?

• NB1:我正在使用Oracle SQL.
• NB2:我不介意是否有一种方法可以使用SELECT而不是UPDATE语句来达到结果.

• NB1: I am using Oracle SQL.
• NB2: I do not mind if there is a way to achieve the result using a SELECT instead of an UPDATE statement.

可复制的示例

SELECT 'Jack' as NAME, 'Under 65' as AGE, 3 as VALUE from dual
UNION ALL
SELECT 'Jack' as NAME, '66-74' as AGE, 5 as VALUE from dual
UNION ALL
SELECT 'John' as NAME, '66-74' as AGE, 7 as VALUE from dual
UNION ALL
SELECT 'John' as NAME, 'Over 75' as AGE, 9 as VALUE from dual
UNION ALL
SELECT 'Gill' as NAME, '25-35' as AGE, 11 as VALUE from dual

推荐答案

您可以使用case when子句定义自定义顺序，然后使用解析性max().这适用于给定的示例:

You can define custom order with case when clause and then use analytic max(). This worked for given examples:

update t1 set age = (
    select max(age) keep (dense_rank last
           order by case when age = 'Over 75'  then 1
                         when age = '66-74'    then 2
                         when age = 'Under 65' then 3
                         when age = '25-35'    then 4
                    end)
    from t1 tx where tx.name = t1.name )

这篇关于仅使用SQL中的MAX函数更新重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！