java - 从两个表中获取相似的列名和计数

Question

我的数据库中有多个表(数千个)，我想检查并查找任何两个给定的表是否具有相似的列名。为此，我创建了 3 个示例表，如下所示:

    table1(song_cast, song_name, song_size)
    table2(song_size, singer, movie_name, song_cast)
    table3(movie_name, singer, song_name, song_cast)

我期望的输出是

    |table1 & table2 | song_cast, song_size | 2 #count of common columns
    |table2 & table3 | singer, song_cast, movie_name | 3
    |table1 & table3 | song_name, song_cast | 2

我将在 java(NetBeans IDE 8.2) 中运行此代码，因此我将在 NetBeans 中获得此输出。

Answer 1

使用几个common table expressions stuff() with select ... for xml path ('') method of string concatenation 。

;with cte as (
select c.*
from information_schema.tables t
  inner join information_schema.columns c
    on t.table_schema = c.table_schema
   and t.table_name = c.table_name
where t.table_type='base table'
)
, match as (
select 
    tables = l.table_name + ' & ' + r.table_name
  , l.column_name
from cte l
  inner join cte r
    on l.column_name = r.column_name
   and l.table_name < r.table_name
)
select 
    tables
  , columns = stuff((
  select ', '+ column_name
  from match i
  where m.tables = i.tables
  for xml path (''), type).value('(./text())[1]','nvarchar(max)')
  ,1,2,'')
  , matches = count(*)
from match m
group by tables

rextester 演示:http://rextester.com/TLQ28814

返回:

+-----------------+-------------------------------+---------+
|     tables      |            columns            | matches |
+-----------------+-------------------------------+---------+
| table1 & table2 | song_cast, song_size          |       2 |
| table1 & table3 | song_cast, song_name          |       2 |
| table2 & table3 | singer, movie_name, song_cast |       3 |
+-----------------+-------------------------------+---------+