查找包含某些字段且不包含另一些字段的表代码,MYSQL代码库,德仔网

代码语言

代码分类

【MYSQL】查找包含某些字段且不包含另一些字段的表

作者:浩宇 / 发布于2018/5/12/ 1028

DROP TABLE IF EXISTS temp1,temp2; 
CREATE TEMPORARY TABLE temp1 
SELECT DISTINCT table_name FROM information_schema.columns a WHERE column_name IN 
('category_l3_name', 
'layout_category_l3_name','erp_category_l3_name') 
and table_schema='bi'
; 


CREATE TEMPORARY TABLE temp2 
SELECT DISTINCT table_name FROM information_schema.columns b 
WHERE column_name IN 
( 
'category_l2_append_key','layout_category_l2_append_key',
'layout_category_l2_append_name','category_l2_append_name' 
) 
and table_schema='bi'
; 


SELECT table_name,column_name
,CONCAT('alter table ',table_name,' add category_l2_append_name varchar(100) AFTER ',column_name,';
','alter table ',table_name,' add category_l2_append_key varchar(30) AFTER ',column_name,';') AS sqltext
FROM 
(
SELECT a.table_name,min(c.column_name) as column_name
 FROM temp1 a JOIN information_schema.columns c
 ON a.table_name=c.table_name AND c.column_name IN 
('category_l3_name', 
'layout_category_l3_name','erp_category_l3_name')
and c.table_schema='bi'
WHERE a.table_name NOT IN 
(SELECT table_name FROM temp2 ) 
group by  a.table_name
)
aa
ORDER BY table_name,column_name
;

试试其它关键字

同语言下

可能有用的

浩宇贡献的其它代码(18)