조인에서 모든 열 이름 앞에 열 이름의 원본 테이블을 붙이는 방법

조인에서 모든 열 이름 앞에 열 이름의 원본 테이블을 붙이는 방법

저는 상당히 끔찍한 레거시 데이터베이스/코드베이스를 분석하고 있습니다.쿼리를 결합함으로써 서버 부하를 줄이려고 합니다(일반적으로 100만 개 이상의 개별 쿼리를 호출하는 이메일 경보 크론 작업 포함).

SELECT * FROM 
class_alerts_holding ah 
INNER JOIN class_listings l ON l.id = ah.lid 
INNER JOIN class_users u ON u.id = ah.uid
LEFT JOIN class_prodimages pi ON pi.pid = ah.lid

120개의 컬럼을 쏟아냅니다...

aid | id | lid | uid | oid | catName | searchtext | alertfreq | listType | id | owner | title | section | shortDescription | description | featured | price | display | hitcount | dateadded | expiration | url | notified | searchcount | repliedcount | pBold | pHighlighted | notes | ...

새로운 쿼리를 작성하는 방법에 대한 분석을 지원하기 위해 결과의 컬럼에 JOIN의 테이블을 부가할 수 있으면 좋겠습니다.

class_alerts_holding.aid | class_alerts_holding.id | class_listings.lid | ...

이것을 달성할 수 있는 방법이 있나요?

할 수 있다

select ah.*, l.*, u.*, pi.* from ...

그러면 컬럼이 적어도 테이블로 순서대로 반환됩니다.

각 두 열 집합을 더 잘 구별하기 위해 다음과 같이 " 구분자" 열을 추가할 수도 있습니다.

select ah.*, ':', l.*, ':', u.*, ':', pi.* from ...

^{(불필요한 경우 명시적 에일리어스를 삭제하기 위해 편집됩니다.주석을 참조해 주세요).}

쿼리의 필드에 이름을 지정하고 별칭을 지정할 수 있습니다.

SELECT     ah.whateverfield1 AS 'ah_field1',
           ah.whateverfield2 AS 'ah_field2',
           l.whateverfield3 AS 'l.field3',
           [....]
FROM       class_alerts_holding ah 
INNER JOIN class_listings l ON l.id = ah.lid 
INNER JOIN class_users u ON u.id = ah.uid
LEFT JOIN  class_prodimages pi ON pi.pid = ah.lid

이렇게 많은 필드가 있는 경우 수동으로 설정하는 것이 조금 어렵지만 이 쿼리를 통해 이 작업을 단순화할 수 있습니다.

SHOW FULL FIELDS FROM your_table_name;

...좋은 텍스트 에디터와 복사 & 페이스트.

조인에서 테이블 이름을 가진 필드 이름의 접두사 및/또는 사후 수정 기능이 ANSI SQL 표준에 포함되어야 한다고 확신합니다.현재 2019년에도 이를 위한 우아한 크로스 플랫폼 방법은 없으며, 남은 것은 보기 흉하고 오류가 발생하기 쉬운 에일리어스 수동 해킹 또는 동적 SQL을 포함하는 플랫폼별 솔루션뿐입니다.「닷스타」(.*)로 표시되는 필드에 커스텀 프리픽스나 포스트픽스를 지정할 수 있으면, 모든 유저가 메리트를 얻을 수 있습니다.이러한 기능을 추가한 후의 선택 예는 다음과 같습니다.

select a.* use prefix,b.* use postfix '_b' from table_a a inner join table_b b on a.id=b.id

디폴트로는 프리픽스 또는 postfix는 테이블명(또는 에일리어스명)과 같으며 원하는 문자열 리터럴로 덮어쓸 수 있습니다.

또한 표준으로 추가해야 하는 문제는 모든 필드를 선택하는 바로 가기인 '별표'(*) 출력에서 특정 필드를 제외할 수 있다는 것입니다.네트워크 데이터 전송 또는 간결성을 줄이기 위해 포함하지 않는 fied 목록에 except 키워드를 추가합니다.다음은 예를 제시하겠습니다.

select * except large_binary_data_field,another_notneeded_field,etc from my_table

이러한 기능을 사용하면 별과 필요하지 않은 몇 개의 필드만 지정하는 것이 아니라 필요한 전체(및 잠재적으로 큰) 필드 목록을 명시적으로 지정할 필요가 없습니다.

이 투고를 읽고 ANSI SQL의 표준적인 영향력자에게 연락할 수 있다면 어떻게 해야 하는지 알고 계시기 바랍니다.)

추신. 또 하나 못생겼지만 적어도 자동화된 범용 다이내믹 SQL 래퍼

psycopg를 사용하는 Python 옹호자를 위해 내가 사용하는 편리한 서브를 소개합니다(SQL 주입이 가능한 경우가 많기 때문에 엄격하게 내부적으로 사용).

def get_table_fields(table,alias,prefix='',suffix='',excluding=''):
    if type(excluding)==str: excluding=excluding.split(',')
    cur.execute('select * from '+table+' where 0=1');cur.fetchall()
    if not (cur.description is None):        
        return ','.join([alias+'.'+col.name+' '+prefix+col.name+suffix for col in cur.description if not (col.name in excluding)])

또한 데이터셋 테이블에서 대용량 데이터 필드를 가져오지 않도록 하기 위해 3개의 테이블을 결합하는 호출 코드:

sql="""select %s,%s,%s from tasks t,features_sets f,datasets d 
        where 
                t.is_active=true and f.is_active=true 
                and f.task=t.id and t.train_dataset=d.id 
    """ % (
        get_table_fields('tasks','t',prefix='ts_'),
        get_table_fields('features_sets','f',prefix='fs_'),
        get_table_fields('datasets','d',prefix='ds_',excluding='data')
    )

나를 위해 힘차게 굴려져

select t.id ts_id,t.project ts_project,t.name ts_name,***,
    fs_id,f.task fs_task,f.name fs_name,f.description fs_description,***,
    d.id ds_id,d.project ds_project,d.name ds_name,***
from tasks t,features_sets f,datasets d 
    where 
        t.is_active=true and f.is_active=true 
        and f.task=t.id and t.train_dataset=d.id 

***는 기타 유용한 필드를 많이 나타냅니다.이들 중 일부는 여러 테이블에서 공통입니다(프리픽스의 필요성을 배제합니다).cur는 분명히 psycopg 커서이며 0=1 조건은 실제 데이터 없이 필드 이름만 검색하기 위한 것입니다.

열의 이름을 동적으로 지정하는 방법은 information_schema를 참조하는 준비된 문을 생성하는 것입니다.이렇게 하면 원하는 결과를 얻을 수 있습니다.

SET @sql = NULL;
SELECT CONCAT(
   'SELECT ',GROUP_CONCAT(c.TABLE_NAME,'.',c.COLUMN_NAME,' AS `',c.TABLE_NAME,'.',c.COLUMN_NAME,'`'),'
    FROM class_alerts_holding 
    INNER JOIN class_listings ON class_listings.id = class_alerts_holding.lid 
    INNER JOIN class_users ON class_users.id = class_alerts_holding.uid
    LEFT JOIN class_prodimages ON class_prodimages.pid = class_alerts_holding.lid'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME IN ('class_alerts_holding','class_listings',
                       'class_users','class_prodimages');    
PREPARE sql_statement FROM @sql;
EXECUTE sql_statement;

GROUP_CONCAT() 함수의 기본 제한은 1024자입니다.따라서 테이블 내의 컬럼 수에 따라서는 준비된 스테이트먼트를 생성하기 위해 이 제한을 높여야 할 수 있습니다.

SET SESSION group_concat_max_len = 1000000;

이 명령어는 필요에 따라 그룹 콘센트 제한을 높입니다.-

쿼리용 필드 세트를 구축하는 것만으로 끝납니다.2020년 시점에서는 아직 지원되지 않습니다.

그러나 게으른 프로그래머이기 때문에 질의에 있는 모든 테이블에 대해 수동으로 이 모든 것을 입력하고 싶지 않은 것이 분명합니다.select 스테이트먼트를 작성하기 위해 쿼리를 작성했습니다.

SELECT
    CONCAT(table_name, ".", column_name, " AS ", CHAR(34), table_name, ".", column_name, CHAR(34)) field_names
FROM
    information_schema.columns
WHERE
    table_schema = "my_database"
    AND table_name IN(
        "table_1",
        "table_2"
    );

다음과 같이 출력됩니다.

| field_names                        |
|------------------------------------|
| table_1.id AS "table_1.id"         |
| table_1.name AS "table_1.name"     |
| table_2.id AS "table_2.id"         |
| table_2.number AS "table_2.number" |

쉽게 that일 파 your that your 。SELECT★★★★★★ 。

이 질문에서 MySQL concat()이 쿼리에서 사용할 컬럼 이름을 만들기 위해 유용한 것을 발견했습니다.이것이 해결책 중 하나라고 생각합니다.

Kolja가 제안한 솔루션을 기반으로 합니다.TM과 AndriyM은 다음과 같이 질문을 작성하는 것이 더 나을 수 있습니다.

select
  '--TABLE_AAA:--', TABLE_AAA.*,
  '--TABLE_BBB:--', TABLE_BBB.*,
  '--TABLE_CCC:--', TABLE_CCC.*,
  '--TABLE_DDD:--', TABLE_DDD.*
from ...

안타깝게도 하나 이상의 테이블이 화면 너비에 들어갈 수 있는 열 이름보다 더 많은 열 이름을 포함하는 경우에는 여전히 충분하지 않습니다(따라서 화면에는 20개의 열이 표시되지만 여전히 테이블 이름이 화면에 표시되지 않습니다).

SQL이 열 이름에 테이블 이름을 자동으로 붙이는 방법을 제공했다면 더 좋았을 것입니다.

@alden-w, 다른 스키마의 동일한 테이블 이름을 혼재시키지 않는 TABLE_SCHEMA 조건을 추가할 수 있습니다.

WHERE c.TABLE_SCHEMA='YOUR_SCHEMA_NAME' AND c.TABLE_NAME IN (....)

CREATE OR REPLACE FUNCTION getAlias (mytable text, my_alias text, my_prefix text)
RETURNS SETOF TEXT AS $$
   SELECT my_alias || column_name || ' as ' || my_prefix
   FROM information_schema.COLUMNS
   WHERE TABLE_NAME = mytable;
$$ LANGUAGE SQL

-- 함수는 db에 쓸 수 있습니다.이 함수는 SQL에서 표준이어야 합니다.

MS SQL의 DarkRob 제안에 따르면 두 테이블이 일부 열 이름을 공유하는 경우 "Markuous column name..." 오류 메시지를 피할 수 있습니다.

DECLARE @cols1 NVARCHAR(max)
SET @cols1 =  (SELECT STUFF(
          (SELECT ', ati.' + [COLUMN_NAME] + ' AS ' + 'ati_' + [COLUMN_NAME] FROM information_schema.columns 
           WHERE [TABLE_NAME] in ('audit_trans_inv') FOR XML PATH('')),1,1,''))

DECLARE @cols2 NVARCHAR(max)
SET @cols2 =  (SELECT STUFF(
          (SELECT ', ti.' + [COLUMN_NAME] + ' AS ' + 'ti_' + [COLUMN_NAME] from information_schema.columns 
           WHERE [TABLE_NAME] in ('transaccion_inv') FOR XML PATH('')),1,1,''))

DECLARE @sql NVARCHAR(max) = '
SELECT TOP 5 ' + @cols1 + ',' + @cols2 + '
FROM [millennium].[AUDIT_TRANS_INV] ati
INNER JOIN [millennium].[TRANSACCION_INV] ti ON [ti].[AUDIT_TRANS_INV] = [ati].[AUDIT_TRANS_INV]

EXEC sp_executesql @sql

테이블 정의에 따라 이동 중에 쿼리를 작성하기 위해 동적 sql을 시도할 수 있습니다.

declare @col varchar(max)
set @col = Select stuff( 
          (select ', ' + column_name + '.' + table_name 
           from information_schema.columns 
           where table_name in ( 'table1', 'table2' ...) for xml 
           path('')),1,1,'')

declare @query nvarchar(max) = '
select ' + @col + ' 
from table1 
inner join table2 on table1.id = table2.id '

exec sp_executesql @query

언급URL : https://stackoverflow.com/questions/13153344/in-a-join-how-to-prefix-all-column-names-with-the-table-it-came-from