tablas - SQL select join: ¿es posible prefijar todas las columnas como ''prefix.*''?

tipos de join mysql (18)

Desarrollando a partir de esta solución , así es como abordaría el problema:

Primero crea una lista de todas las declaraciones AS :

DECLARE @asStatements varchar(8000) SELECT @asStatements = ISNULL(@asStatements + '', '','''') + QUOTENAME(table_name) + ''.'' + QUOTENAME(column_name) + '' AS '' + ''['' + table_name + ''.'' + column_name + '']'' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''TABLE_A'' OR TABLE_NAME = ''TABLE_B'' ORDER BY ORDINAL_POSITION

Luego úsalo en tu consulta:

EXEC(''SELECT '' + @asStatements + '' FROM TABLE_A a JOIN TABLE_B b USING (some_id)'');

Sin embargo, esto podría necesitar modificaciones porque algo similar solo se prueba en SQL Server. Pero este código no funciona exactamente en SQL Server porque USING no es compatible.

Comente si puede probar / corregir este código, por ejemplo, MySQL.

Me pregunto si esto es posible en SQL. Supongamos que tiene dos tablas A y B, y hace una selección en la tabla A y se une en la tabla B:

SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);

Si la tabla A tiene las columnas ''a_id'', ''name'' y ''some_id'', y la tabla B tiene ''b_id'', ''name'' y ''some_id'', la consulta devolverá las columnas ''a_id'', ''name'', ''some_id '','' b_id '','' nombre '','' some_id ''. ¿Hay alguna manera de ponerle un prefijo a los nombres de columna de la tabla B sin enumerar cada columna individualmente? El equivalente de esto:

SELECT a.*, b.b_id as ''b.b_id'', b.name as ''b.name'', b.some_id as ''b.some_id'' FROM TABLE_A a JOIN TABLE_B b USING (some_id);

Pero, como se mencionó, sin listar cada columna, algo así como:

SELECT a.*, b.* as ''b.*'' FROM TABLE_A a JOIN TABLE_B b USING (some_id);

Básicamente algo para decir, "prefija cada columna devuelta por b. * Con ''algo''". ¿Es esto posible o no tengo suerte?

¡Gracias de antemano por tu ayuda!

EDITAR: el consejo sobre no usar SELECT * y demás es un consejo válido pero no es relevante en mi contexto, así que por favor se adhieren al problema en cuestión: ¿es posible agregar un prefijo (una constante especificada en la consulta SQL) a todo el nombres de columna de una tabla en una unión?

EDITAR: mi objetivo final es poder hacer un SELECCIONAR * en dos tablas con una combinación, y ser capaz de decir, a partir de los nombres de las columnas que obtengo en mi conjunto de resultados, qué columnas provienen de la tabla A y de qué columnas provienen de la tabla B. Una vez más, no quiero tener que listar columnas individualmente, necesito poder hacer una SELECCIÓN *.

Entiendo totalmente por qué es necesario, al menos para mí es útil durante el prototipado rápido cuando hay muchas tablas necesarias para unir, incluidas muchas combinaciones internas. Tan pronto como el nombre de una columna sea el mismo en una segunda tarjeta salvaje de campo "joinedtable. *", Los valores de campo de la tabla principal se anulan con los valores de la tabla unida. Error propenso, frustrante y una violación de DRY al tener que especificar manualmente los campos de tabla con alias una y otra vez ...

Aquí hay una función de PHP (Wordpress) para lograr esto a través de la generación de código junto con un ejemplo de cómo usarlo. En el ejemplo, se usa para generar rápidamente una consulta personalizada que proporcionará los campos de una publicación relacionada de wordpress a la que se hizo referencia a través de un campo avanzado de campos personalizados .

La salida:

SELECT `campaigns`.`ID` AS `campaigns.ID`, `campaigns`.`post_author` AS `campaigns.post_author`, `campaigns`.`post_date` AS `campaigns.post_date`, `campaigns`.`post_date_gmt` AS `campaigns.post_date_gmt`, `campaigns`.`post_content` AS `campaigns.post_content`, `campaigns`.`post_title` AS `campaigns.post_title`, `campaigns`.`post_excerpt` AS `campaigns.post_excerpt`, `campaigns`.`post_status` AS `campaigns.post_status`, `campaigns`.`comment_status` AS `campaigns.comment_status`, `campaigns`.`ping_status` AS `campaigns.ping_status`, `campaigns`.`post_password` AS `campaigns.post_password`, `campaigns`.`post_name` AS `campaigns.post_name`, `campaigns`.`to_ping` AS `campaigns.to_ping`, `campaigns`.`pinged` AS `campaigns.pinged`, `campaigns`.`post_modified` AS `campaigns.post_modified`, `campaigns`.`post_modified_gmt` AS `campaigns.post_modified_gmt`, `campaigns`.`post_content_filtered` AS `campaigns.post_content_filtered`, `campaigns`.`post_parent` AS `campaigns.post_parent`, `campaigns`.`guid` AS `campaigns.guid`, `campaigns`.`menu_order` AS `campaigns.menu_order`, `campaigns`.`post_type` AS `campaigns.post_type`, `campaigns`.`post_mime_type` AS `campaigns.post_mime_type`, `campaigns`.`comment_count` AS `campaigns.comment_count`, `venues`.`ID` AS `venues.ID`, `venues`.`post_author` AS `venues.post_author`, `venues`.`post_date` AS `venues.post_date`, `venues`.`post_date_gmt` AS `venues.post_date_gmt`, `venues`.`post_content` AS `venues.post_content`, `venues`.`post_title` AS `venues.post_title`, `venues`.`post_excerpt` AS `venues.post_excerpt`, `venues`.`post_status` AS `venues.post_status`, `venues`.`comment_status` AS `venues.comment_status`, `venues`.`ping_status` AS `venues.ping_status`, `venues`.`post_password` AS `venues.post_password`, `venues`.`post_name` AS `venues.post_name`, `venues`.`to_ping` AS `venues.to_ping`, `venues`.`pinged` AS `venues.pinged`, `venues`.`post_modified` AS `venues.post_modified`, `venues`.`post_modified_gmt` AS `venues.post_modified_gmt`, `venues`.`post_content_filtered` AS `venues.post_content_filtered`, `venues`.`post_parent` AS `venues.post_parent`, `venues`.`guid` AS `venues.guid`, `venues`.`menu_order` AS `venues.menu_order`, `venues`.`post_type` AS `venues.post_type`, `venues`.`post_mime_type` AS `venues.post_mime_type`, `venues`.`comment_count` AS `venues.comment_count` FROM wp_posts AS campaigns LEFT JOIN wp_postmeta meta1 ON (meta1.meta_key = ''venue'' AND campaigns.ID = meta1.post_id) LEFT JOIN wp_posts venues ON (venues.post_status = ''publish'' AND venues.post_type = ''venue'' AND venues.ID = meta1.meta_value) WHERE 1 AND campaigns.post_status = ''publish'' AND campaigns.post_type = ''campaign'' LIMIT 1 Array ( [0] => stdClass Object ( [campaigns.ID] => 33 [campaigns.post_author] => 2 [campaigns.post_date] => 2012-01-16 19:19:10 [campaigns.post_date_gmt] => 2012-01-16 19:19:10 [campaigns.post_content] => Lorem ipsum [campaigns.post_title] => Lorem ipsum [campaigns.post_excerpt] => [campaigns.post_status] => publish [campaigns.comment_status] => closed [campaigns.ping_status] => closed [campaigns.post_password] => [campaigns.post_name] => lorem-ipsum [campaigns.to_ping] => [campaigns.pinged] => [campaigns.post_modified] => 2012-01-16 21:01:55 [campaigns.post_modified_gmt] => 2012-01-16 21:01:55 [campaigns.post_content_filtered] => [campaigns.post_parent] => 0 [campaigns.guid] => http://example.com/?p=33 [campaigns.menu_order] => 0 [campaigns.post_type] => campaign [campaigns.post_mime_type] => [campaigns.comment_count] => 0 [venues.ID] => 84 [venues.post_author] => 2 [venues.post_date] => 2012-01-16 20:12:05 [venues.post_date_gmt] => 2012-01-16 20:12:05 [venues.post_content] => Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. [venues.post_title] => Lorem ipsum venue [venues.post_excerpt] => [venues.post_status] => publish [venues.comment_status] => closed [venues.ping_status] => closed [venues.post_password] => [venues.post_name] => lorem-ipsum-venue [venues.to_ping] => [venues.pinged] => [venues.post_modified] => 2012-01-16 20:53:37 [venues.post_modified_gmt] => 2012-01-16 20:53:37 [venues.post_content_filtered] => [venues.post_parent] => 0 [venues.guid] => http://example.com/?p=84 [venues.menu_order] => 0 [venues.post_type] => venue [venues.post_mime_type] => [venues.comment_count] => 0 ) )

Entiendo totalmente tu problema sobre nombres de campos duplicados.

Lo necesitaba también hasta que codifiqué mi propia función para resolverlo. Si usa PHP, puede usarlo o codificar el suyo en el idioma en el que está utilizando si cuenta con las siguientes facilidades.

El truco aquí es que mysql_field_table() devuelve el nombre de la tabla y mysql_field_name() el campo para cada fila en el resultado si se obtiene con mysql_num_fields() para que pueda mezclarlos en una nueva matriz.

Esto prefija todas las columnas;)

Saludos,

function mysql_rows_with_columns($query) { $result = mysql_query($query); if (!$result) return false; // mysql_error() could be used outside $fields = mysql_num_fields($result); $rows = array(); while ($row = mysql_fetch_row($result)) { $newRow = array(); for ($i=0; $i<$fields; $i++) { $table = mysql_field_table($result, $i); $name = mysql_field_name($result, $i); $newRow[$table . "." . $name] = $row[$i]; } $rows[] = $newRow; } mysql_free_result($result); return $rows; }

Esta pregunta es muy útil en la práctica. Solo es necesario enumerar todas las columnas explícitas en la programación de software, donde se debe prestar especial atención para tratar todas las condiciones.

Imagine que al depurar o tratar de usar DBMS como una herramienta de oficina diaria, en lugar de implementar algo alterable de la infraestructura subyacente abstracta del programador específico, necesitamos codificar muchos SQL. El escenario se puede encontrar en todas partes, como la conversión de bases de datos, la migración, la administración, etc. La mayoría de estos SQL se ejecutarán una sola vez y nunca se volverán a usar, dar nombres a cada columna es una pérdida de tiempo. Y no olvide que la invención de SQL no es solo para uso de los programadores.

Por lo general, crearé una vista de utilidad con los nombres de columna prefijados, aquí está la función en pl / pgsql, no es fácil, pero puede convertirla a otros lenguajes de procedimiento.

-- Create alias-view for specific table. create or replace function mkaview(schema varchar, tab varchar, prefix varchar) returns table(orig varchar, alias varchar) as $$ declare qtab varchar; qview varchar; qcol varchar; qacol varchar; v record; sql varchar; len int; begin qtab := ''"'' || schema || ''"."'' || tab || ''"''; qview := ''"'' || schema || ''"."av'' || prefix || tab || ''"''; sql := ''create view '' || qview || '' as select''; for v in select * from information_schema.columns where table_schema = schema and table_name = tab loop qcol := ''"'' || v.column_name || ''"''; qacol := ''"'' || prefix || v.column_name || ''"''; sql := sql || '' '' || qcol || '' as '' || qacol; sql := sql || '', ''; return query select qcol::varchar, qacol::varchar; end loop; len := length(sql); sql := left(sql, len - 2); -- trim the trailing '', ''. sql := sql || '' from '' || qtab; raise info ''Execute SQL: %'', sql; execute sql; end $$ language plpgsql;

Ejemplos:

-- This will create a view "avp_person" with "p_" prefix to all column names. select * from mkaview(''public'', ''person'', ''p_''); select * from avp_person;

Estoy en el mismo barco que OP - Tengo docenas de campos de 3 tablas diferentes a las que me estoy inscribiendo, algunas de las cuales tienen el mismo nombre (es decir, identificación, nombre, etc.). No quiero enumerar cada campo, por lo que mi solución fue aliar los campos que comparten un nombre y usar select * para aquellos que tienen un nombre único.

Por ejemplo :

tabla a: id, nombre, campo1, campo2 ...

tabla b: id, nombre, campo3, campo4 ...

seleccione a.id como aID, a.nombre como aNombre, a. *, b.id como bid, b.name como bName, b. * .....

Al acceder a los resultados, nosotros los nombres con alias de estos campos e ignoramos los nombres "originales".

Tal vez no sea la mejor solución pero funciona para mí ... estoy usando mysql

Hay dos maneras en que puedo pensar para que esto suceda de una manera reutilizable. Una es cambiar el nombre de todas sus columnas con un prefijo para la tabla de la que provienen. Lo he visto muchas veces, pero realmente no me gusta. Encuentro que es redundante, causa mucho tipeo y siempre puede usar alias cuando necesita cubrir el caso de un nombre de columna que no tiene un origen claro.

Por otro lado, lo que recomendaría que haga en su situación si está comprometido a ver esto, es crear vistas para cada tabla que alias los nombres de las tablas. Luego te unes contra esas vistas, en lugar de las tablas. De esta forma, puede usar * si lo desea, puede usar las tablas originales con los nombres de columna originales, si lo desea, y también facilita las consultas subsiguientes porque ya ha realizado el trabajo de cambio de nombre en las vistas.

Finalmente, no tengo claro por qué necesita saber de qué tabla proviene cada una de las columnas. ¿Esto importa? En última instancia, lo que importa es la información que contienen. Si el ID de usuario proviene de la tabla de Usuario o la tabla de Pregunta de Usuario realmente no importa. Importa, por supuesto, cuando necesita actualizarlo, pero en ese punto ya debe conocer su esquema lo suficientemente bien como para determinarlo.

Hay una respuesta directa a su pregunta para aquellos que usan la API MySQL C-API.

Dado el SQL:

SELECT a.*, b.*, c.* FROM table_a a JOIN table_b b USING (x) JOIN table_c c USING (y)

Los resultados de ''mysql_stmt_result_metadata ()'' proporcionan la definición de los campos de su consulta SQL preparada en la estructura MYSQL_FIELD []. Cada campo contiene los siguientes datos:

char *name; /* Name of column (may be the alias) */ char *org_name; /* Original column name, if an alias */ char *table; /* Table of column if column was a field */ char *org_table; /* Org table name, if table was an alias */ char *db; /* Database for table */ char *catalog; /* Catalog for table */ char *def; /* Default value (set by mysql_list_fields) */ unsigned long length; /* Width of column (create length) */ unsigned long max_length; /* Max width for selected set */ unsigned int name_length; unsigned int org_name_length; unsigned int table_length; unsigned int org_table_length; unsigned int db_length; unsigned int catalog_length; unsigned int def_length; unsigned int flags; /* Div flags */ unsigned int decimals; /* Number of decimals in field */ unsigned int charsetnr; /* Character set */ enum enum_field_types type; /* Type of field. See mysql_com.h for types */

Tome nota de los campos: catalog, table, org_name

Ahora sabe qué campos de su SQL pertenecen a qué esquema (también conocido como catálogo) y tabla. Esto es suficiente para identificar genéricamente cada campo desde una consulta SQL de varias tablas, sin tener que alias nada.

Se muestra un producto real SqlYOG para utilizar estos datos exactos de tal manera que puedan actualizar de forma independiente cada tabla de una combinación de múltiples tablas, cuando los campos PK están presentes.

La única base de datos que sé que hace esto es SQLite, dependiendo de la configuración que usted configure con PRAGMA full_column_names y PRAGMA short_column_names . Ver http://www.sqlite.org/pragma.html

De lo contrario, todo lo que puedo recomendar es buscar columnas en un conjunto de resultados por posición ordinal en lugar de por columna, si es demasiado trabajo escribir los nombres de las columnas en su consulta.

Este es un buen ejemplo de por qué es una mala práctica usar SELECT * porque eventualmente tendrá que escribir todos los nombres de columna de todos modos.

Entiendo la necesidad de admitir columnas que pueden cambiar el nombre o la posición, pero el uso de comodines hace que sea más difícil , no más fácil.

Los productos de base de datos diferentes le darán respuestas diferentes; pero te estás haciendo daño si llevas esto muy lejos. Es mucho mejor que elija las columnas que desea, y les da sus propios alias para que la identidad de cada columna sea clara como el cristal, y puede diferenciarlos en los resultados.

No hay un estándar SQL para esto.

Sin embargo, con la generación de código (ya sea a petición como las tablas se crean o se modifican o en tiempo de ejecución), puede hacerlo con bastante facilidad:

CREATE TABLE [dbo].[_329931_a]( [id] [int] IDENTITY(1,1) NOT NULL, [col2] [nchar](10) NULL, [col3] [nchar](10) NULL, [col4] [nchar](10) NULL, CONSTRAINT [PK__329931_a] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[_329931_b]( [id] [int] IDENTITY(1,1) NOT NULL, [col2] [nchar](10) NULL, [col3] [nchar](10) NULL, [col4] [nchar](10) NULL, CONSTRAINT [PK__329931_b] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] DECLARE @table1_name AS varchar(255) DECLARE @table1_prefix AS varchar(255) DECLARE @table2_name AS varchar(255) DECLARE @table2_prefix AS varchar(255) DECLARE @join_condition AS varchar(255) SET @table1_name = ''_329931_a'' SET @table1_prefix = ''a_'' SET @table2_name = ''_329931_b'' SET @table2_prefix = ''b_'' SET @join_condition = ''a.[id] = b.[id]'' DECLARE @CRLF AS varchar(2) SET @CRLF = CHAR(13) + CHAR(10) DECLARE @a_columnlist AS varchar(MAX) DECLARE @b_columnlist AS varchar(MAX) DECLARE @sql AS varchar(MAX) SELECT @a_columnlist = COALESCE(@a_columnlist + @CRLF + '','', '''') + ''a.['' + COLUMN_NAME + ''] AS ['' + @table1_prefix + COLUMN_NAME + '']'' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table1_name ORDER BY ORDINAL_POSITION SELECT @b_columnlist = COALESCE(@b_columnlist + @CRLF + '','', '''') + ''b.['' + COLUMN_NAME + ''] AS ['' + @table2_prefix + COLUMN_NAME + '']'' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table2_name ORDER BY ORDINAL_POSITION SET @sql = ''SELECT '' + @a_columnlist + '' ,'' + @b_columnlist + '' FROM ['' + @table1_name + ''] AS a INNER JOIN ['' + @table2_name + ''] AS b ON ('' + @join_condition + '')'' PRINT @sql -- EXEC (@sql)

No puedo hacer esto sin aliasing, simplemente porque, ¿cómo vas a hacer referencia a un campo en la cláusula where, si ese campo existe en las 2 o 3 tablas a las que te estás uniendo? No será claro para mysql a cuál estás tratando de hacer referencia.

O puede usar Red Gate SQL Refactor o SQL Prompt, que expande su SELECT * en listas de columnas con un clic en el botón Tabulador

entonces en tu caso, si escribes SELECCIONAR * DESDE A UNIR B ... Ir al final de *, botón de tabulación, voila! verá SELECCIONAR A.column1, A.column2, ...., B.column1, B.column2 FROM A JOIN B

Aunque no es gratis

Parece que la respuesta a su pregunta es no, sin embargo, un truco que puede usar es asignar una columna ficticia para separar cada nueva tabla. Esto funciona especialmente bien si está recorriendo un conjunto de resultados para una lista de columnas en un lenguaje de scripts como Python o PHP.

SELECT '''' as table1_dummy, table1.*, '''' as table2_dummy, table2.*, '''' as table3_dummy, table3.* FROM table1 JOIN table2 ON table2.table1id = table1.id JOIN table3 ON table3.table1id = table1.id

Me doy cuenta de que esto no responde exactamente a su pregunta, pero si usted es un codificador, esta es una gran manera de separar tablas con nombres de columna duplicados. Espero que esto ayude a alguien.

Recientemente me encontré con este problema en NodeJS y Postgres.

Enfoque ES6

No hay ninguna función de RDBMS que conozca que brinde esta funcionalidad, así que creé un objeto que contiene todos mis campos, por ejemplo:

const schema = { columns: [''id'',''another_column'',''yet_another_column''] }

Definió un reductor para concatenar las cadenas junto con un nombre de tabla:

const prefix = (table, columns) => columns.reduce((previous, column) => { previous.push(table + ''.'' + column + '' AS '' + table + ''_'' + column); return previous; }, []);

Esto devuelve una matriz de cadenas. Llámalo para cada tabla y combina los resultados:

const columns_joined = [...prefix(''tab1'',schema.columns), ...prefix(''tab2'',schema.columns)];

Salida la declaración SQL final:

console.log(''SELECT '' + columns_joined.join('','') + '' FROM tab1, tab2 WHERE tab1.id = tab2.id'');

Resolví un problema similar al cambiar el nombre de los campos en las tablas involucradas. Sí, tuve el privilegio de hacer esto y comprender que todos pueden no tenerlo. Agregué el prefijo a cada campo dentro de una tabla que representa el nombre de la tabla. Por lo tanto, el SQL publicado por OP se mantendría sin cambios,

SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);

y todavía da los resultados esperados - facilidad de identificar a qué tabla pertenecen los campos de salida.

Si está preocupado por los cambios en el esquema, esto podría funcionar para usted: 1. Ejecute una consulta ''DESCRIBE tabla'' en todas las tablas involucradas. 2. Use los nombres de los campos devueltos para construir dinámicamente una cadena de nombres de columna con el prefijo alias elegido.

Veo dos posibles situaciones aquí. En primer lugar, desea saber si existe un estándar SQL para esto, que puede usar en general independientemente de la base de datos. No no hay. En segundo lugar, desea saber con respecto a un producto dbms específico. Entonces necesitas identificarlo. Pero imagino que la respuesta más probable es que recibirás algo así como "a.id, b.id", ya que así es como necesitarías identificar las columnas en tu expresión SQL. Y la forma más fácil de averiguar cuál es el valor predeterminado es solo enviar una consulta y ver lo que obtienes. Si desea especificar qué prefijo viene antes del punto, puede usar "SELECT * FROM a AS my_alias", por ejemplo.

select * generalmente crea un código incorrecto, ya que las columnas nuevas tienden a agregarse o el orden de las columnas cambia en las tablas con bastante frecuencia, lo que generalmente rompe la selección * de una manera muy sutil. Entonces, listar columnas es la solución correcta.

En cuanto a cómo hacer su consulta, no estoy seguro acerca de mysql, pero en sqlserver puede seleccionar los nombres de columna de syscolumns y construir dinámicamente la cláusula de selección.