Postgres从多个表中找到最大列值 [英] Postgres find max column value from multiple tables

问题描述

我有具有特定列名的表的列表，例如

I have list of tables that have specific column names like

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'column1'

我需要找到每个表的column1最大值。我希望得到类似以下的结果

I need to find the max value of column1 for each tables. I expect result like the following

|--------|--------------|
| Table  |  Max column1 |
|--------|--------------|
| Table1 |     100      |
| Table2 |     200      |
|  ...   |     ...      |
|--------|--------------|

如何构造查询？

推荐答案

您可以使用所有表的行计数方法：

select t.table_name, 
       (xpath('/row/max/text()', xmax))[1]::text::int
from (
  SELECT table_name, data_type, 
         query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, '') as xmax
  FROM information_schema.columns
  WHERE column_name = 'column1'
    and table_schema = 'public'
) as t;

query_to_xml（）运行从.. 中为查询返回的每一列选择max（..）。其结果类似于：

query_to_xml() runs a select max(..) from .. for each column returned from the query. The result of that is something like:

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <max>42</max>
</row>

然后使用 xpath（）函数从XML中提取值。派生表（子查询）并不是真正需要的，但是它使 xpath（）表达式更具可读性（在我看来）。

The xpath() function is then used to extract the value from the XML. The derived table (sub-query) is not really needed, but makes the xpath() expression more readable (in my opinion).

这篇关于Postgres从多个表中找到最大列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！