在多个表中搜索并在结果行中显示表名 [英] Search across multiple tables and also display table name in resulting rows

问题描述

我如何构建一个 SQL 语句来运行多个平面不相关的表，并用选择的结果和结果来自的表的名称来显示结果.

How do I structure an SQL statement to run across multiple flat unrelated tables and display the result with the result of the select and the name of the table where the result came from.

场景是这样的，我有几个表，每个表都有相同的列名.这是我从外部接收到的数据，并按原样存储在不同的表中.

The scenario is such that I have several tables with the same column name in each. It is data that I have received from outside parties that I store as it is in different tables.

相同的表看起来像:

Table 1: pid, parent_name, student_name, student_number, class_name, columnN
Table 2: pid, previous_school, previous_school, student_number, columnN
Table 3: pid, student_name, student_number, parent_name, column4, columnN
Table 14: pid, student_number, parent_name, column4, columnN
Table N: pid, previous_school, parent_name, column4, columnN

我需要一个在所有表中搜索 student_name 的 SQL 语句伪代码:对于每张表，找到一个叫john doe的学生，把你得到结果的那行和你找到结果的表返回给我

I need an SQL statement that searches for student_name across all tables In pseudo code: for each table, find a student named john doe and return to me the row where you got the result and the table where you found the result

在下面的演示中给出结果:

Give the result in the following presentation:

john doe, Table 1, pid
john doe, Table 9, pid

为了使它有点复杂，列 student_name 可能不在所有表中，因此如果在那里找不到该列，则查询需要继续进行.

To make it a bit complicated, the column student_name might not be in all tables so the query needs to proceed graciously if doesn't find the column there.

推荐答案

您正在寻找动态 SQL.自动从系统目录中组合您的查询:

You are looking for dynamic SQL. Assemble your query from the system catalog automatically:

SELECT string_agg('SELECT student_name, '''
                   || c.oid::regclass || ''' AS tbl, pid FROM '
                   || c.oid::regclass
                   || $$ WHERE student_name = 'John Doe'$$
                 , E'
UNION ALL
')
FROM   pg_namespace n
JOIN   pg_class     c ON c.relnamespace = n.oid
WHERE  n.nspname = 'public'         -- schema name where your tables lie
AND    c.relname LIKE 't%'          -- and / or filter table names
AND    EXISTS (
   SELECT 1 FROM pg_attribute 
   WHERE  attrelid = c.oid
   AND    attname = 'student_name'  -- make sure column exists
   AND    NOT attisdropped          -- and is alive
   );

产生查询字符串:

SELECT student_name, 'tbl1' AS tbl, pid FROM tbl1 WHERE student_name = 'John Doe'
UNION ALL
SELECT student_name, 'tbl2' AS tbl, pid FROM tbl2 WHERE student_name = 'John Doe'
UNION ALL
SELECT student_name, 'tbl3' AS tbl, pid FROM tbl3 WHERE student_name = 'John Doe'
...

然后在第二次调用中运行它或使用 PL/pgSQL 函数使用 EXECUTE.示例:
从表中选择一组动态列并获取每个列的总和

Then run it in a second call or completely automate it with a PL/pgSQL function using EXECUTE. Example:
Select a dynamic set of columns from a table and get the sum for each

此查询产生安全带有防止 SQL 注入的清理标识符的代码.(此处对 oid::regclass 的解释.)

This query produces safe code with sanitized identifiers preventing SQL injection. (Explanation for oid::regclass here.)

有更多相关的答案.使用搜索.

顺便说一句，student_name LIKE 'John Doe' 中的 LIKE 毫无意义.没有通配符，只需使用 =.

BTW, LIKE in student_name LIKE 'John Doe' is pointless. Without wildcards, just use =.

这篇关于在多个表中搜索并在结果行中显示表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！