SQL心得

由已知，推未知

分析sql语句之子查询查询+合并查询：

select dest_ne_cuid from topo_link  where orig_ne_cuid = 'TRANS_ELEMENT-8a5d89913097c01601309c71d8f73567'and dest_ne_cuid in (select related_element_cuid from net_to_element_node where group_num='2'and related_net_cuid='TRANS_SUB_NETWORK-8a5d898c30e4373d0130f2a9f1db0020')
UNION
select orig_ne_cuid from topo_link  where dest_ne_cuid = 'TRANS_ELEMENT-8a5d89913097c01601309c71d8f73567'and orig_ne_cuid in (select related_select A.related_subnet_cuid from sn_ne A join trans_element B on A.related_ne_cuid=B.cuid join trans_sub_network C on A.related_subnet_cuid=C.cuid  where B.cuid='TRANS_ELEMENT-8a5d899130f2a3dd0130f32a534e005b'and C.label_cn='分散核心层'element_cuid from net_to_element_node where group_num='2'and related_net_cuid='TRANS_SUB_NETWORK-8a5d898c30e4373d0130f2a9f1db0020')

分析sql语句之条件查询：

select A.related_subnet_cuid from sn_ne A ,trans_element B,trans_sub_network C where  A.related_ne_cuid=B.cuid and A.related_subnet_cuid=C.cuid  and B.cuid='TRANS_ELEMENT-8a5d899130f2a3dd0130f32a534e005b'and C.label_cn='分散核心层'

分析sql语句之常见错误：

String sql="select * from TRANS_ELEMENT where cuid='"+origordestcuid+"'"，这是一个关键的问题。

 一个informix不同的数据库之间进行同步数据（测试和正式库）：

insert into  ne_to_ems  select * from tnms300db@tnms_sh:informix.ne_to_ems where EMS_CUID ='NMS_SYSTEM-8a5d898c2cece14a012cece391010001' or EMS_CUID ='NMS_SYSTEM-8a5d898c2d21ce6c012d25b9b198000e' ;

 

数据库客户端：

squirrel（小松鼠）万能的客户端，只要对应数据库的驱动就行，什么mysql，oracle，informix，sqlserver...都行。

 

注：

"select ch.name,ch.code,ch.backsourcestrategy,ip.address,(select ifnull(count(flexicache.id),0) from channel_flexicache cf join application flexicache
on cf.flexicache_id=flexicache.id,online_flexicache of" +" where (cf.repeatCount<-1 or flexicache.id =of.flexiCache_id) and cf.channel_id=ch.id ) as fcount
from channel ch join channel_sourceip csip on csip.channel_id = ch.id join ip ip on ip.id=csip.ip_id where ch.code in('"+ channelCodes + "')
and ch.backsourcestrategy<>'ORIGDOMAIN' ";