跨多个表进行搜索，并在生成的行中显示表名 [英] Search across multiple tables and also display table name in resulting rows

问题描述

如何将SQL语句结构化以跨多个平面无关的表运行，并显示结果与select的结果以及结果来自哪个表的名称。

场景是这样的，我有几个表在每个列中具有相同的列名称。这是我从外部各方收到的数据，我存储在不同的表格。

相同的表格如下：

$ b表1：pid，parent_name，student_name，student_number，class_name，columnN
表2：pid，previous_school，previous_school，student_number，columnN
表3 ：pid，student_name，student_number，parent_name，column4，columnN
表14：pid，student_number，parent_name，column4，columnN
表N：pid，previous_school，parent_name，column4，columnN

我需要一个SQL语句，在所有表中搜索<​​code> student_name
在每个表格的伪代码：中，找到一个名叫john doe的学生，并返回到你得到结果的行和找到结果的表

在以下演示中给出结果：

  john doe，表1 ，pid 
 john doe，表9，pid 
  

为了使它有点复杂，列 student_name 可能不在所有表中，所以如果没有找到列，查询需要慷慨地执行。

解决方案

您正在寻找动态SQL。自动从系统目录汇编查询：

  SELECT string_agg（'SELECT student_name，'''
 || c $ E E，，，，，，，，，，，，，，，，，，，，，，，，，，，，，，， E'\NUNION ALL\\\
'）
 FROM pg_namespace n 
 JOIN pg_class c ON c.relnamespace = n.oid 
 WHERE n.nspname ='public' - 模式名称你的表是
 AND c.relname LIKE't％' - 和/或过滤器表名称
 AND EXISTS（
 SELECT 1 FROM pg_attribute 
 WHERE attrelid = c.oid 
 AND attname ='student_name' - 确保列存在
 AND NOT attisdropped  - 并且还活着
）; 
  

生成查询字符串：

  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 。示例：

从表格中选择一组动态的列，并获得每个列的总和

此查询生成 安全代码与消毒标识符阻止SQL注入。 （ oid :: regclass 的说明）。

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

LIKE 在 student_name LIKE'John Doe'是无意义的。没有通配符，只需使用 = 。

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.

Same tables look like:

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

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

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.

解决方案

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'\nUNION ALL\n')
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
   );

Produces the query string:

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'
...

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

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

There are more related answers. Use a search.

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

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