- SELECT
- *
- FROM
- (
- SELECT
- tpfo.id AS folderId,
- tds.id AS sourceId,
- tdt.id AS tableId,
- tdtc.id AS columnId,
- tpfo.NAME AS subjectName,#主题名称
- tds.NAME AS sourceName,#数据源名
- tdt.table_name AS tableName,#表名
- tdt.table_alias AS tableAlias,#表别名
- tdt.table_remark AS tableRemark,#表描述
- tdtc.column_name AS columnName,#字段名
- tdtc.column_alias AS columnAlias,#字段别名
- tdtc.column_remark AS columnRemark #字段描述
- FROM
- tb_project_folder tpfo
- LEFT JOIN tb_project_file tpf ON tpf.folder_id = tpfo.id
- LEFT JOIN tb_data_source tds ON tds.id = tpf.datasourceId
- LEFT JOIN tb_ds_table tdt ON tdt.ds_id = tds.id
- AND tdt.domain_id = tpfo.id
- LEFT JOIN tb_ds_table_column tdtc ON tdt.id = tdtc.table_id
- WHERE
- tds.data_type = 1
- AND tds.deleted = 0
- AND tdt.is_delete = 0
- AND tdtc.is_delete = 0
- AND (
- INSTR( tdtc.column_name, ‘CODE’ ) > 0
- OR INSTR( tdtc.column_remark, ‘CODE’ ) > 0
- OR INSTR( tdtc.column_alias, ‘CODE’ ) > 0
- ) UNION ALL
- SELECT
- tpfo.id AS folderId,
- tds.id AS sourceId,
- tdt.id AS tableId,
- NULL AS columnId,
- tpfo.NAME AS subjectName,#主题名称
- tds.NAME AS sourceName,#数据源名
- tdt.table_name AS tableName,#表名
- tdt.table_alias AS tableAlias,#表别名
- tdt.table_remark AS tableRemark,#表描述
- NULL AS columnName,#字段名
- NULL AS columnAlias,#字段别名
- NULL AS columnRemark #字段描述
- FROM
- tb_project_folder tpfo
- LEFT JOIN tb_project_file tpf ON tpf.folder_id = tpfo.id
- LEFT JOIN tb_data_source tds ON tds.id = tpf.datasourceId
- LEFT JOIN tb_ds_table tdt ON tdt.ds_id = tds.id
- AND tdt.domain_id = tpfo.id
- WHERE
- tds.data_type = 1
- AND tds.deleted = 0
- AND tdt.is_delete = 0
- AND (
- INSTR( tdt.table_name, ‘CODE’ ) > 0
- OR INSTR( tdt.table_remark, ‘CODE’ ) > 0
- OR INSTR( tdt.table_alias, ‘CODE’ ) > 0
- )
- GROUP BY
- tpfo.id,
- tdt.id
- ) tb
- ORDER BY
- subjectName,
- sourceName,
- tableName,
- columnName
作者 admin
张宴银，大数据开发工程师