如何使用最频繁的值更新表格

Question

我有一个更新查询，应该使用另一张表中最常见的值来更新一个字段，以下是我在PostgreSQL中的操作：

UPDATE TABLE1 T1 
SET COLUMN_B = (SELECT COLUMN_B
                         FROM 
                         (SELECT COLUMN_A,COLUMN_B, COUNT(1) AS FREQUENCY
                                 FROM TABLE2 T2
                                GROUP BY COLUMN_A,COLUMN_B
                                 ORDER BY COLUMN_A,FREQUENCY DESC) QUERY1
                          WHERE QUERY1.COLUMN_A= T1.COLUMN_A

                    GROUP BY COLUMN_A,COLUMN_B,FREQUENCY
                    ORDER BY FREQUENCY DESC LIMIT 1
                   )

在PostgreSQL中，这个查询很好用，我想使用“rownum=1”在Oracle中进行相同的查询，但是我遇到了几个问题：

1. 无法在更新的select语句中放置ORDER BY。
2. 如果我决定将ORDER BY放在嵌套的select中（QUERY1），则嵌套的select无法理解对表T1的引用（T2.COLUMN_A=T1.COLUMN_A会出现错误，即T1.COLUMN_A不是有效标识符）。

如何在Oracle中实现这个查询？我的意思是，希望为每个COLUMN_A填充T1.COLUMN_B，该值是从T2的COLUMN_B中最常见的公共值。

感谢您的任何帮助或建议。

- Dany Y

Answer 1

我相信有更简单的方法来完成这个任务，但这应该是一个很好的起点，而且应该在Oracle和Postgres中都能工作。

UPDATE TABLE1 T1
SET COLUMN_B = 
select COLUMN_B from (
(SELECT DISTINCT COLUMN_A, COLUMN_B
    FROM TABLE2 T2
    WHERE T2.COLUMN_A= T1.COLUMN_A
    GROUP BY COLUMN_A, COLUMN_B
    HAVING COUNT(1) = (
        SELECT MAX(CNT) FROM (
            SELECT COLUMN_B, COUNT(1) AS CNT
            FROM TABLE2 T3
            WHERE T3.COLUMN_A= T2.COLUMN_A)
        )
    )
)

- bpgergo

---

谢谢你的解决方案，但是它仍然给我一个错误提示：“T1.COLUMN_A无效标识符”，来自最后一个嵌套选择WHERE T3.COLUMN_A= T1.COLUMN_A。看起来你不能从嵌套选择中引用表进行更新，而且我找不到没有嵌套选择的频率。 - Dany Y

这很奇怪。根据文档，这是一个有效的更新语句：UPDATE employee_temp SET (first_name, last_name) = (SELECT first_name, last_name FROM employees WHERE employee_id = employee_temp.employee_id) 文档: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/update_statement.htm - bpgergo

是的，这个可以正常工作，但如果选择包含嵌套的选择，则子查询无法引用employee_temp。这是我在测试中发现的。 - Dany Y

也许你只能引用一级。我编辑了答案，请再试一次，因为目前我无法访问Oracle数据库。 - bpgergo

同样的问题，我认为你是对的，你只能引用一级，更新不是问题。 - Dany Y

谢谢！它起作用了！！！我只需要把where条件去掉。 SELECT MAX(CNT) FROM ( SELECT COLUMN_A,COLUMN_B, COUNT(1) AS CNT FROM TABLE2 group by COLUMN_A,COLUMN_B ) T3 WHERE T3.SITE_ID = T2.SITE_ID 如果您能在答案中编辑它，那就太好了，这样我就可以将其标记为已回答。 - Dany Y

Answer 2

这可以通过使用Oracle的分析函数来完成；不确定是否可以在PostgreSQL中运行相同的代码。

创建目标表：

SQL> create table t23
  2    as select deptno, cast (null as number(7,2)) max_sal from dept
  3  /

Table created.
SQL> 

现在，更新如下：

SQL> update t23
  2  set max_sal = ( select sal from
  3                   ( select deptno, sal
  4                     , row_number() over (partition by deptno order by sal desc) rn
  5                      from emp )
  6                 where rn = 1
  7                 and deptno = t23.deptno )
  8  /

6 rows updated.

SQL>

以下是结果:

SQL> select * from t23
  2  /

    DEPTNO    MAX_SAL
---------- ----------
        10       5000
        20       3000
        30       3750
        40
        50       4500
        60

6 rows selected.

SQL> 

仅确认结果...

SQL> select deptno, max(sal)
  2  from emp
  3  where sal is not null
  4  group by deptno
  5  order by deptno
  6  /

    DEPTNO   MAX(SAL)
---------- ----------
        10       5000
        20       3000
        30       3750
        50       4500

SQL> 

- APC

---

+1 对于 row_number() over (partition by deptno order by sal desc)，这是一个更好的解决 order by 限制的方法。 - bpgergo