[SQL] 纯文本查看 复制代码
//是否有大小写
//OP1查询
String op1_qtime = "select * from op1 where time between '"+start+ "' and '" + end + "'";//查询最后一个记录
String op1_result = "select temp.code,temp.zz,temp.xm,temp.time,max(temp.id) from ('"+op1_qtime+"') as temp group by temp.code";//嵌套查询
//OP2查询
String op2_qtime = "select * from op2 where time between '" + start + "' and '" + end + "'";//查询最后一个记录
String op2_result = "select temp.code,temp.hd,temp.rj,temp.jd,temp.ds,temp.time,max(temp.id) from ('" + op2_qtime + "') as temp group by temp.code";//嵌套查询
//OP3查询
String op3_qtime = "select * from op3 where time between '" + start + "' and '" + end + "'";//查询最后一个记录
String op3_result = "select temp.code,temp.flowstatus,temp.speedstatus,temp.flow,temp.speed,temp.voice,temp.power,temp.time,max(temp.id) from ('" + op3_qtime + "') as temp group by temp.code";//嵌套查询
//OP4查询
String op4_qtime = "select * from op4 where time between '" + start + "' and '" + end + "'";//查询最后一个记录
String op4_result = "select temp.code,temp.resis,temp.interval,temp.time,max(temp.id) from ('" + op4_qtime + "') as temp group by temp.code";//嵌套查询
//OP5查询
String op5_qtime = "select * from op5 where time between '" + start + "' and '" + end + "'";//查询最后一个记录
String op5_result = "select temp.code,temp.interval,temp.switch,temp.time,max(temp.id) from ('" + op5_qtime + "') as temp group by temp.code";//嵌套查询
//OP6查询
String op6_qtime = "select * from op6 where time between '" + start + "' and '" + end + "'";//查询最后一个记录
String op6_result = "select temp.code,temp.flowstatus,temp.speedstatus,temp.flow,temp.speed,temp.direction,temp.voice,temp.surface,temp.defect,temp.outline,temp.damage,temp.code1,temp.shell,temp.time,max(temp.id) from ('" + op6_qtime + "') as temp group by temp.code";//嵌套查询
//full join
//idea 不如Datagrid加入6个时间,,,嵌套查询要括号括起来
String op1_op2 = "select op1.code as a,op1.zz as b,op1,zm as c,op1.time as op1time,op2.hd as d,op2.rj as e,op2.jd as f,op2.ds as g,op2.time as op2time from ('"+op1_result+"') as op1 inner join ('"+op2_result+"') as op2 on op1.code=op2.code";
String op1_op2_op3 = "select op12.a,op12.b,op12.c,op12.op1time,op12.d,op12.e,op12.f,op12.g,op12.op2time,op3.flowstatus as h,op3.speedstatus as i,op3.flow as j,op3.speed as k,op3.voice as l,op3.power as m,op3.time as op3time from ('"+op1_op2+"') as op12 inner join ('"+op3_result+"') as op3 on op12.a=op3.code";
String op1_op2_op3_op4 = "select op123.a,op123.b,op123.c,op123.op1time,op123.d,op123.e,op123.f,op123.g,op123.op2time,op123.h,op123.i,op123.j,op123.k,op123.l,op123.m,op123.op3time,op4.resis as n,op4.interval as o,op4.time as op4time from('"+op1_op2_op3+"') as op123 inner join ('"+op4_result+"') as op4 on op123.a=op4.code";
String op1_op2_op3_op4_op5 = "select op1234.a,op1234.b,op1234.c,op1234.op1time,op1234.d,op1234.e,op1234.f,op1234.g,op1234.op2time,op1234.h,op1234.i,op1234.j,op1234.k,op1234.l,op1234.m,op1234.op3time,op1234.n,op1234.o,op1234.op4time,op5.interval as p,op5.switch as q,op5.time as op5time from ('"+op1_op2_op3_op4+"') as op1234 inner join ('"+op5_result+"') as op5 on op1234.a=op5.code";
String op1_op2_op3_op4_op5_op6 = "select op12345.a,op12345.b,op12345.c,op12345.op1time,op12345.d,op12345.e,op12345.f,op12345.g,op12345.op2time,op12345.h,op12345.i,op12345.j,op12345.k,op12345.l,op12345.m,op12345.op3time,op12345.n,op12345.o,op12345.op4time,op12345.p,op12345.q,op12345.op5time,op6.flowstatus as r,op6.speedstatus as s,op6.flow as t,op6.speed as u,op6.direction as v,op6.voice as w,op6.surface as x,op6.defect as y,op6.outline as z,op6.damage as aa,op6.code1 as bb,op6.shell as cc,op6.time as op6time from ('"+op1_op2_op3_op4_op5+"') as op12345 inner join ('"+op6_result+"') as op6 on op12345.a=op6.code";
//result结构a,b,c,op1time,d,e,f,g,op2time,h,i,j,k,l,m,op3time,n,o,op4time,p,q,op5time,r,s,t,u,v,w,x,y,z,aa,bb,cc,op6time
SqlConnection sqlCnt = new SqlConnection(conn);
sqlCnt.Open();
SqlCommand sql = new SqlCommand(op1_op2_op3_op4_op5_op6, sqlCnt);
发表于 2020-4-25 20:51
|
发表于 2020-4-25 21:23
