孙建伟 20:24:47 try{ con = DataBaseConnection.getConnection(); stmt = con.createStatement();
for(int i=0;i<sjhs.length;i=i+2){
String Select="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh = '"+sjhs[i]+"' and endsjh = '"+sjhs[i+1]+"'"; pstmt = con.prepareStatement(Select); rst = pstmt.executeQuery(); if(rst.next()){
} else{ String sqlSelect="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i]+"' and endsjh >= '"+sjhs[i]+"'"; System.out.println(sqlSelect); pstmt = con.prepareStatement(sqlSelect); rst = pstmt.executeQuery(); if(rst.next()) { String sqlSelect2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i+1]+"' and endsjh >= '"+sjhs[i+1]+"'"; System.out.println(sqlSelect2); pstmt = con.prepareStatement(sqlSelect2); rst2 = pstmt.executeQuery();
if(rst2.next()) {
String sql2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i+1]+"' and id not in (select id from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i]+"')"; System.out.println(sql2); pstmt = con.prepareStatement(sql2); rst3 = pstmt.executeQuery(); while(rst3.next()) { String sql3="delete from t_blackwhitelist where id = '"+rst3.getString("id")+"'"; System.out.println(sql3); pstmt.execute(sql3); }
String sql="UPDATE t_blackwhitelist SET STARTSJH = '"+rst.getString("startsjh")+"',ENDSJH = '"+rst2.getString("endsjh")+"' where id = '"+rst.getString("id")+"'"; System.out.println(sql); pstmt.execute(sql);
} else{
String sql2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh < '"+sjhs[i+1]+"' and id not in (select id from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i]+"')"; System.out.println(sql2); pstmt = con.prepareStatement(sql2); rst3 = pstmt.executeQuery(); while(rst3.next()) { String sql3="delete from t_blackwhitelist where id = '"+rst3.getString("id")+"'"; System.out.println(sql3); pstmt.execute(sql3); }
String sql="UPDATE t_blackwhitelist SET STARTSJH = '"+rst.getString("startsjh")+"',ENDSJH = '"+sjhs[i+1]+"' where id = '"+rst.getString("id")+"'"; System.out.println(sql); pstmt.execute(sql); } }
else{ String sqlSelect2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i+1]+"' and endsjh >= '"+sjhs[i+1]+"'"; System.out.println(sqlSelect2); pstmt = con.prepareStatement(sqlSelect2); stDel = con.createStatement(); rst2 = pstmt.executeQuery(); if(rst2.next()) {
String sql2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i+1]+"' and id not in (select id from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh < '"+sjhs[i]+"')"; System.out.println(sql2); pstmt = con.prepareStatement(sql2); rst3 = pstmt.executeQuery(); while(rst3.next()) { String sql3="delete from t_blackwhitelist where id = '"+rst3.getString("id")+"'"; System.out.println(sql3); stDel.execute(sql3); }
String sql2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh < '"+sjhs[i+1]+"' and id not in (select id from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh < '"+sjhs[i]+"')"; System.out.println(sql2); pstmt = con.prepareStatement(sql2); rst3 = pstmt.executeQuery(); while(rst3.next()) { String sql3="delete from t_blackwhitelist where id = '"+rst3.getString("id")+"'"; System.out.println(sql3); System.out.println("3333"); stDel.execute(sql3); }
如果输入1到10(此时库里1到10)
如果输入3到11(此时库里1到11)
如果输入13到20(此时库里1到20)
大概就是这样,写一条SQL语句能够应付以上等条件,希望高手赐教!
这个问题比较经典
接收到新的范围后,小的数与low_code比较,如果小的数<low_code,则用小的数覆盖low_code;大数与high_code比较,如果大的数>high_code,则用大的数覆盖high_code。
只能写一条语句吗-.-
x<inputx?x:inputx
y>inputy?y:inputy
select * from atable where colom>x and colom<y
接收到新的范围后,小的数与low_code比较,如果小的数<low_code,则用小的数覆盖low_code;大数与high_code比较,如果大的数>high_code,则用大的数覆盖high_code。如果输入的大数比low_code小呢?
updata tabel set min=LEAST(min,minval),max=GREATEST(max,maxval) where ...
字段名s1 s2
输入变量x1 x2更新语句
"update table set s1=s1+(abs(s1-"+x1+")+(s1-"+x1+"))/2,s2=s2+(abs(s2-"+x2+")+(s2-"+x2+"))/2" ;原理
x大于y时 x=x-(|x-y|+(x-y))/2;这时x就为小的那个也就是y
x小于y时 x=x-(|x-y|+(x-y))/2;这时x仍为小的那个也就是x
求大的那个相同道理
x大于y时 x=x+(|x-y|+(x-y))/2;
x小于y时 x=x+(|x-y|+(x-y))/2;根据 负数的绝对值是本身的相反数JF 呵呵
孙建伟 20:24:47
try{
con = DataBaseConnection.getConnection();
stmt = con.createStatement();
for(int i=0;i<sjhs.length;i=i+2){
String Select="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh = '"+sjhs[i]+"' and endsjh = '"+sjhs[i+1]+"'";
pstmt = con.prepareStatement(Select);
rst = pstmt.executeQuery();
if(rst.next()){
}
else{
String sqlSelect="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i]+"' and endsjh >= '"+sjhs[i]+"'";
System.out.println(sqlSelect);
pstmt = con.prepareStatement(sqlSelect);
rst = pstmt.executeQuery();
if(rst.next()) {
String sqlSelect2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i+1]+"' and endsjh >= '"+sjhs[i+1]+"'";
System.out.println(sqlSelect2);
pstmt = con.prepareStatement(sqlSelect2);
rst2 = pstmt.executeQuery();
if(rst2.next()) {
String sql2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i+1]+"' and id not in (select id from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i]+"')";
System.out.println(sql2);
pstmt = con.prepareStatement(sql2);
rst3 = pstmt.executeQuery();
while(rst3.next()) {
String sql3="delete from t_blackwhitelist where id = '"+rst3.getString("id")+"'";
System.out.println(sql3);
pstmt.execute(sql3);
}
String sql="UPDATE t_blackwhitelist SET STARTSJH = '"+rst.getString("startsjh")+"',ENDSJH = '"+rst2.getString("endsjh")+"' where id = '"+rst.getString("id")+"'";
System.out.println(sql);
pstmt.execute(sql);
}
else{
String sql2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh < '"+sjhs[i+1]+"' and id not in (select id from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i]+"')";
System.out.println(sql2);
pstmt = con.prepareStatement(sql2);
rst3 = pstmt.executeQuery();
while(rst3.next()) {
String sql3="delete from t_blackwhitelist where id = '"+rst3.getString("id")+"'";
System.out.println(sql3);
pstmt.execute(sql3);
}
String sql="UPDATE t_blackwhitelist SET STARTSJH = '"+rst.getString("startsjh")+"',ENDSJH = '"+sjhs[i+1]+"' where id = '"+rst.getString("id")+"'";
System.out.println(sql);
pstmt.execute(sql);
}
}
else{ String sqlSelect2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i+1]+"' and endsjh >= '"+sjhs[i+1]+"'";
System.out.println(sqlSelect2);
pstmt = con.prepareStatement(sqlSelect2);
stDel = con.createStatement();
rst2 = pstmt.executeQuery();
if(rst2.next()) {
String sql2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh <= '"+sjhs[i+1]+"' and id not in (select id from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh < '"+sjhs[i]+"')";
System.out.println(sql2);
pstmt = con.prepareStatement(sql2);
rst3 = pstmt.executeQuery();
while(rst3.next()) {
String sql3="delete from t_blackwhitelist where id = '"+rst3.getString("id")+"'";
System.out.println(sql3);
stDel.execute(sql3);
}
String sql="INSERT INTO T_BLACKWHITELIST(STARTSJH,ENDSJH,HEIBAI) VALUES(?,?,?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,sjhs[i]);
pstmt.setString(2,rst2.getString("endsjh"));
pstmt.setString(3,mdleixing);
pstmt.executeUpdate();
}else{
String sql2="select * from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh < '"+sjhs[i+1]+"' and id not in (select id from t_blackwhitelist where heibai = '"+mdleixing+"' and startsjh < '"+sjhs[i]+"')";
System.out.println(sql2);
pstmt = con.prepareStatement(sql2);
rst3 = pstmt.executeQuery();
while(rst3.next()) {
String sql3="delete from t_blackwhitelist where id = '"+rst3.getString("id")+"'";
System.out.println(sql3);
System.out.println("3333");
stDel.execute(sql3);
}
String sql="INSERT INTO T_BLACKWHITELIST(STARTSJH,ENDSJH,HEIBAI) VALUES(?,?,?)";
System.out.println(sql);
pstmt = con.prepareStatement(sql);
pstmt.setString(1,sjhs[i]);
pstmt.setString(2,sjhs[i+1]);
pstmt.setString(3,mdleixing);
pstmt.executeUpdate();
}
}
}
}
}
catch (Exception e)
{e.printStackTrace();
}
finally{
try{if(pstmt!=null) pstmt.close();}
catch(Exception e){}
try{if(con!=null) con.close();}
catch(Exception e){}
}
RequestDispatcher requestDispatcher = request.getRequestDispatcher("Showsjh?mdleixing="+mdleixing);
requestDispatcher.forward(request,response);
%>
字段名s1 s2
输入变量x1 x2update table set s1=case when s1<x1 then s1 else x1 end,s2=case when s2>x2 then s2 else x2 end