excel文件中处理成sql语句

--------------------------------------------excel中:--------------------------------------------

所有的文本连接
=TEXTJOIN("','",,A1:A262)


excel中新增公式
=CONCATENATE("insert into index_entity (index_name,type) VALUES ('",A1,"','",IF(C1="正向",1,IF(C1="负向",2,3)),"');")

=CONCATENATE("insert into index_entity (index_name,type,index_description) VALUES ('",E2,"','",1,"','",K2,"');")


=CONCATENATE("insert into index_rule_entity (name,description,type,apply_level,index_basis,dept_name) VALUES ('",A1,"-",B1,"','",E1,"',",IF(C1="",1,2),",",3,",'",G1,"','",IF(F1="","",F1),"');")

=CONCATENATE("insert into formula_entity (formula_name,formula_source,status,index_type) VALUES ('",A1,"-",B1,"','",IF(D1="","",D1),"',",0,",",1,");")
--------------------------------------------mysql中:--------------------------------------------
----规则和公式关联
INSERT INTO index_rule_formula (rule_id,formula_name,formula_source)
SELECT r.id rule_id,IFNULL(f.formula_name,'') formula_name,IFNULL(f.formula_source,'') formula_source FROM index_rule_entity r LEFT JOIN formula_entity f on r.name = f.formula_name
WHERE r.id not in (select rule_id from index_rule_formula) and f.formula_name is not null;

UPDATE index_rule_entity r , index_rule_formula f SET r.formula_id=f.id WHERE r.id = f.rule_id;

----规则和指标关联
INSERT INTO index_rule_relation (index_id,rule_id)
SELECT DISTINCT i.id index_id,r.id rule_id FROM index_rule_entity r LEFT JOIN index_entity i on r.name like CONCAT('%',i.index_name)
WHERE 1=1 and r.id not in (select rule_id from index_rule_relation) and i.id not in (select index_id from index_rule_relation)


---查询重复的关系
SELECT  r.index_id,r.rule_id FROM index_rule_relation r WHERE 1=1 and rule_id=266;

select * from  index_entity t where 1=1
and (id =222 or id = 355);

select * from index_rule_entity where 1=1
#and id =253
and name like  '%社区居民电子健康档案建档率(%)%'

SELECT  r.index_id,r.rule_id,count(1) FROM index_rule_relation r GROUP BY r.rule_id HAVING count(1)>1;

DELETE from  index_rule_relation where 1=1 and index_id =355 and rule_id = 135;

SELECT  e.id,e.index_name,r.index_id,r.rule_id FROM index_entity e LEFT JOIN index_rule_relation r on e.id=r.index_id  WHERE 1=1 ORDER BY r.rule_id


INSERT INTO index_system_relation (system_id,parent_id,index_id,rule_id) 
select 1 system_id,IFNULL((select r.id FROM index_entity e left JOIN index_system_relation r on e.id=r.index_id WHERE 1=1 and e.index_name='概况' limit 1),0) parent_id 
,t.id index_id,IFNULL(r.rule_id,0) rule_id
from index_entity t LEFT JOIN index_rule_relation r on t.id=r.index_id where 1=1 and t.index_name in ('概况','经济','文化','民生','生态')

posted @   全琪俊  阅读(73)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· PowerShell开发游戏 · 打蜜蜂
· 凌晨三点救火实录:Java内存泄漏的七个神坑,你至少踩过三个!
点击右上角即可分享
微信分享提示