这段代码估计用不到了，写的很简单，用了最直观的思路替换参数。
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class 替换字符串参数_S4_Test {
public static void main(String[] args) throws Exception {
String str="select rownum, bb.* from (select :a, :b + 4, 4 + :c, 5 + :b, to_char(:c, 'yyy-mm-dd'), to_date(:v, 'yyyy-mm-dd') from a where a.order_id = :b and b in (:b) and c in (:b, :l) and d in (:l, 0) and e in (0, :l) and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd') and b.prod_spec_id = :MODEL_ID and extract(month from b.done_date) = extract(month from to_date(:b, ' yyyy - mm ')) and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date) and a.order_type = 80 and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd') and extract(month from b.done_date) = extract(month from to_date(:b, 'yyyy-mm')) and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date) and extract(month from b.done_date) = extract(month from to_date(:b, ' yyyy - mm ')) union select extract(month from to_date(:b, ' yyyy - mm ')), to_char(:v, 'yyy-m-dd'), to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd') from b group by a having extract(month from b.done_date) = :b and b = :b union all select extract(month from to_date(:b, ' yyyy - mm ')), to_char(:v, 'yyy-m-dd'), to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd') from b group by a having(extract(month from b.done_date) = :b and b = :b) and b = :k) bb group by aa, bb desc having(aa = :k and bb > :b + 5) ";
str = addSqlBlank(str);
List<String> sqlList = splitSqlByUnion(str);
str=replaceSqlNullValue(sqlList);
System.out.println("替换结果为="+str);
}
public static String replaceSqlNullValue(List<String> sqlList)throws Exception {
if(sqlList==null||sqlList.size()==0){
return "";
}
StringBuffer sb2=new StringBuffer();
for (String sql : sqlList) {
// 取真实的from 是否带where 是否带having
int existsIndex = sql.lastIndexOf(" exists(");
int havingIndex = sql.indexOf(" having");
int fromIndex = sql.lastIndexOf(" from ");
int whereIndex = sql.indexOf(" where ");
int extractIndex = sql.lastIndexOf(" extract(");
String selectSqlStr = null;
if (havingIndex != -1 && fromIndex > havingIndex) {
fromIndex = sql.substring(0, havingIndex).lastIndexOf(" from ");
}
if (whereIndex != -1 && extractIndex != -1) {
fromIndex = sql.substring(0, whereIndex).lastIndexOf(" from ");
}
if (whereIndex != -1 && fromIndex > existsIndex) {
while (existsIndex != -1 && fromIndex > existsIndex) {
String tmpStr = sql.substring(0, existsIndex);
fromIndex = tmpStr.lastIndexOf(" from ");
existsIndex = tmpStr.lastIndexOf(" exists(");
}
}
selectSqlStr = sql.substring(0, fromIndex + 6);
sql = sql.substring(fromIndex + 6);
sb2.append(replaceNullValueAfterSelect(selectSqlStr));
sb2.append(removeAllErrorConnect(replaceValueAfterFrom(sql)));
}
return sb2.toString();
}
public static String replaceNullValueAfterSelect(String sql) {
if (sql.indexOf(":") == -1) {
return sql;
}
int position = sql.indexOf(":");
StringBuffer before = new StringBuffer();
StringBuffer after = new StringBuffer();
int beforeBracket = 0;// 匹配括号数量
boolean isConnect = false;
char bracketBeforeChar = '\0', bracketAfterChar = '\0';
//直接拿到字符串
while (position != -1) {
// 向←直到空格或者开始
for (int i = position - 1; i >= 0; i--) {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
if (sql.charAt(i) == ',' || sql.charAt(i) == ' ') {
break;
} else if (sql.charAt(i) == '+' || sql.charAt(i) == '-') {
isConnect = true;
before.append(sql.charAt(i));
bracketBeforeChar = sql.charAt(i);
break;
} else {
before.append(sql.charAt(i));
}
}
// 向→直到空格或者最后
for (int i = position; i < sql.length(); i++) {
if (beforeBracket != 0) {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
after.append(sql.charAt(i));
continue;
}
if (sql.charAt(i) == '+' || sql.charAt(i) == '-') {
bracketAfterChar = sql.charAt(i);
after.append(sql.charAt(i));
isConnect = true;
break;
}
if (sql.charAt(i) != ' ' && sql.charAt(i) != ',') {
after.append(sql.charAt(i));
} else {
break;
}
}
after.insert(0, before.reverse());
if (isConnect) {
sql = sql.replace(
after.toString(),
String.valueOf(
bracketBeforeChar + "'0'" + bracketAfterChar)
.replace('\0', ' '));
} else {
sql = sql.replace(after.toString(), "' '");
}
position = sql.indexOf(":");
before.setLength(0);
after.setLength(0);
beforeBracket = 0;
isConnect = false;
bracketAfterChar = '\0';
bracketBeforeChar = '\0';
}
after = null;
before = null;
sql = sql.replaceAll("(extract\\s*\\()([^)]*'\\s+')(\\s*\\))", "' '");;
return new String(sql);
}
public static String replaceValueAfterFrom(String sql) throws Exception {
if (sql.indexOf(":") == -1) {
return sql;
}
StringBuffer result = new StringBuffer();
//含连接符
String[] tmpStrArr = splitByStrNormal(sql, "(?<=\\s+)(and|or)(?=\\s+|\\s*\\()");
for (String subStr : tmpStrArr) {
if (subStr.indexOf(":") == -1) {
if (subStr.replaceAll("(?<=\\s+|^)(and|or)(?=\\s+|\\)|$)", "").trim().length() == 0) {
result.append(' ');
} else {
result.append(subStr);
}
} else if (subStr.indexOf(" in ") != -1) {
result.append(replaceNullParaWithIn(subStr));
} else {
result.append(replaceParaNormalWithBlank(subStr));
}
}
return result.toString();
}
public static String replaceParaNormalWithBlank(String sql) throws Exception {
int position = sql.indexOf(":");
if (position == -1) {
return sql;
}
// 忽略extract
if (sql.indexOf("extract") != -1) {
return removeExtract(sql);
}
if (sql.indexOf("to_date") == -1 && sql.indexOf("to_char") == -1) {
if (sql.indexOf("+") != -1 || sql.indexOf("-") != -1) {
return replaceParaWithConnect(sql);
}
}
StringBuffer before = new StringBuffer();
StringBuffer after = new StringBuffer();
int leftBracket =0, rightBracket =0;
// 括号匹配
int beforeBracket = 0;// 匹配括号数量
int leftIndex=0,rightIndex=sql.length();
while (position != -1) {
leftBracket =Math.abs(sql.substring(0, position).replaceAll("\\(", "").length()-sql.substring(0, position).replaceAll("\\)", "").length());
rightBracket =Math.abs(sql.substring(position).replaceAll("\\(", "").length()-sql.substring(position).replaceAll("\\)", "").length());
if(leftBracket>rightBracket){
for (int i = 0; i < position; i++) {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
if(beforeBracket==leftBracket-rightBracket){
leftIndex=i+1;
break;
}
}
}else if(leftBracket<rightBracket){
for(int i=sql.length()-1;i>position;i--){
if (sql.charAt(i) == '(') {
beforeBracket--;
} else if (sql.charAt(i) == ')') {
beforeBracket++;
}
if(beforeBracket==rightBracket-leftBracket){
rightIndex=i;
break;
}
}
}
// 向←直到空格或者开始
for (int i = position - 1; i >=leftIndex; i--) {
if (sql.charAt(i) != ' ') {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
before.append(sql.charAt(i));
} else {
break;
}
}
// 向→直到空格或者最后
for (int i = position; i <rightIndex; i++) {
if (beforeBracket != 0) {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
after.append(sql.charAt(i));
continue;
}
if (sql.charAt(i) != ' ' && sql.charAt(i) != ')') {
after.append(sql.charAt(i));
} else {
break;
}
}
after.insert(0, before.reverse());
sql = sql.replace(after.toString(), "");
position = sql.indexOf(":");
before.setLength(0);
after.setLength(0);
beforeBracket = 0;
leftIndex=0;
rightIndex=sql.length();
}
before.setLength(0);
after.setLength(0);
after = null;
before = null;
if(sql.indexOf("(")==-1&&sql.indexOf(")")==-1){
return sql.replaceAll("(?<=\\s+|^)(and|or)(?=\\s+|\\)|$)", "");
}
return sql;
}
public static String removeExtract(String sql) throws Exception {
int beforeBracket=0;
int position=sql.indexOf(":");
String subStr=sql.substring(0,position);
int whereIndex=subStr.lastIndexOf(" where ");
int havingIndex=subStr.lastIndexOf(" having ");
int havingIndex2=subStr.lastIndexOf(" having( ");
StringBuffer result=new StringBuffer();
//删除extract
if(whereIndex!=-1){
result.append(sql.substring(0, whereIndex+" where ".length()));
sql=sql.substring(whereIndex+" where ".length());
}else if(havingIndex!=-1){
result.append(sql.substring(0, havingIndex+" having ".length()));
sql=sql.substring(havingIndex+" having ".length());
}else if(havingIndex2!=-1){
result.append(sql.substring(0, havingIndex2+" having(".length()));
sql=sql.substring(havingIndex2+" having(".length());
}
boolean paramFlag=false,bracketEnd=false;
for(int i=Math.max(0,havingIndex2),len=sql.length();i<len;i++){
if(sql.charAt(i)=='('){
beforeBracket++;
bracketEnd=true;
continue;
}else if(sql.charAt(i)==')'){
beforeBracket--;
continue;
}
if(sql.charAt(i)==':'){
paramFlag=true;
}
if(bracketEnd&¶mFlag&&beforeBracket==0){
if(sql.charAt(i)==' '||sql.charAt(i)==')'){
result.append(sql.substring(i));
break;
}
}
}
return replaceNullParaWithIn(result.toString());
}
// 含<>的连接符的变量替换为0
public static String replaceParaWithConnect(String sql) {
int position = sql.indexOf(":");
StringBuffer before = new StringBuffer();
StringBuffer after = new StringBuffer();
while (position != -1) {
if (sql.substring(0, position).indexOf(">") == -1
&&sql.substring(0, position).indexOf("<") == -1
&&sql.substring(0, position).indexOf("!=") == -1) {
// 向←直到连接符
for (int i = position - 1; i >= 0; i--) {
if (sql.charAt(i) != '(') {
before.append(sql.charAt(i));
} else {
break;
}
}
// 向→直到连接符或者最后
for (int i = position; i < sql.length(); i++) {
if (sql.charAt(i) == ' ' || sql.charAt(i) == ')') {
break;
} else {
after.append(sql.charAt(i));
}
}
after.insert(0, before.reverse());
sql = sql.replace(after.toString(), "");
} else {
// 向←直到连接符
for (int i = position - 1; i >= 0; i--) {
if (sql.charAt(i) != '=' && sql.charAt(i) != '<'
&& sql.charAt(i) != '>' && sql.charAt(i) != '+'
&& sql.charAt(i) != '-') {
before.append(sql.charAt(i));
} else {
break;
}
}
// 向→直到连接符或者最后
for (int i = position; i < sql.length(); i++) {
if (sql.charAt(i) == '+' || sql.charAt(i) == '-'
|| sql.charAt(i) == ')') {
break;
} else {
after.append(sql.charAt(i));
}
}
after.insert(0, before.reverse());
sql = sql.replace(after.toString(), "'0'");
}
position = sql.indexOf(":");
before.setLength(0);
after.setLength(0);
}
before.setLength(0);
after.setLength(0);
after = null;
before = null;
sql=sql.replaceAll("(\\(\\s*)(and|or)(\\s+)", "( ");
sql=sql.replaceAll("(\\s*)(and|or)(\\s*\\(\\s*\\))", " ");
return new String(sql);
}
// 替换in括号内的参数
public static String replaceNullParaWithIn(String sql) throws Exception {
int position = sql.indexOf(":");
if (position == -1 || sql.indexOf("in") == -1) {
return removeInErrorSem(sql);
}
StringBuffer before = new StringBuffer();
StringBuffer after = new StringBuffer();
while (position != -1) {
// 向←直到空格或者开始
for (int i = position - 1; i >= 0; i--) {
if (sql.charAt(i) != ' ' && sql.charAt(i) != ',') {
before.append(sql.charAt(i));
} else {
break;
}
}
// 向→直到空格或者最后
for (int i = position; i < sql.length(); i++) {
if (sql.charAt(i) != ' ' && sql.charAt(i) != ','
&& sql.charAt(i) != ')') {
after.append(sql.charAt(i));
} else {
break;
}
}
after.insert(0, before.reverse());
sql = sql.replace(after.toString(), "");
position = sql.indexOf(":");
before.setLength(0);
after.setLength(0);
}
before.setLength(0);
after.setLength(0);
after = null;
before = null;
sql = removeInErrorSem(sql);
sql = sql.replaceAll(",\\s*\\)", ")");
sql = sql.replaceAll("in\\s*\\(\\s*\\)", "in()");
sql = removeInBlankBracket(sql);
return new String(sql);
}
// 清除in括号内多余的逗号
public static String removeInErrorSem(String str) throws Exception {
int position = str.indexOf(" in");
if (position == -1) {
return str;
}
StringBuffer sb = new StringBuffer();
boolean isStart = false;
int endIndex = str.length();
while (position != -1) {
sb.append(str.substring(0, position + 3));
for (int i = position + 3; i < str.length(); i++) {
if (str.charAt(i) == '(') {
sb.append(str.charAt(i));
continue;
}
if (str.charAt(i) == ')') {
sb.append(str.charAt(i));
endIndex = i;
break;
}
if (str.charAt(i) != ',') {
sb.append(str.charAt(i));
if (str.charAt(i) != ' ') {
isStart = true;
}
} else if (isStart && str.charAt(i) == ',') {
sb.append(str.charAt(i));
isStart = false;
}
}
str = str.substring(endIndex + 1);
endIndex = str.length();
position = str.indexOf(" in");
}
if (str != null) {
sb.append(str);
}
str = sb.toString();
sb.setLength(0);
sb = null;
return new String(str);
}
// 清除in空括号
public static String removeInBlankBracket(String sql) {
if (sql.indexOf("in()") == -1) {
return sql;
}
int position = sql.indexOf("in()");
StringBuffer sb = new StringBuffer();
int startIndex = 0;
boolean isParam = false, isChange = false;
;
while (position != -1) {
for (int i = position - 1; i >= 0; i--) {
if (!isParam) {
if (sql.charAt(i) != ' ') {
isParam = true;
continue;
}
}
if (isParam) {
if (sql.charAt(i) == '(') {
isParam = false;
startIndex = i + 1;
isChange = true;
break;
} else if (i >= 2 && sql.charAt(i) == 'd'
&& sql.charAt(i - 1) == 'n'
&& sql.charAt(i - 2) == 'a') {
isParam = false;
isChange = true;
startIndex = i - 2;
break;
} else if (i >= 1 && sql.charAt(i) == 'r'
&& sql.charAt(i - 1) == 'o') {
isParam = false;
isChange = true;
startIndex = i - 1;
break;
}
}
}
if (isChange) {
sb.append(sql.substring(0, startIndex));
} else {
sb.append(sql.substring(0, position + 4));
}
startIndex = 0;
isParam = false;
isChange = false;
sql = sql.substring(position + 4);
position = sql.indexOf("in()");
}
if (sql != null) {
sb.append(sql);
}
sql = sb.toString();
sb.setLength(0);
sb = null;
sql = sql.replaceAll("(\\(\\s*)(and|or)(\\s+)", "( ");
sql = sql.replaceAll("(\\s*|^)(and|or)(\\s*\\(\\s*\\))", " ");
return new String(sql);
}
public static String removeAllErrorConnect(String sql) throws Exception {
sql = replaceSqlWithRegex(sql,"(\\(\\s*)(and|or)(\\s*\\)?)", new int[]{1,3});
sql = replaceSqlWithRegex(sql,"(\\s+)(and|or)(\\s*\\))", new int[]{1,3});
sql = replaceSqlWithRegex(sql,"(\\s+and|or)(\\(\\s*,?\\s*\\))(\\s*,?$?)", new int[]{3});// 清空空括号
sql = replaceSqlWithRegex(sql, "(\\s*)(\\(\\s*,?\\s*\\))(\\s*,?$?)", new int[]{3});// 清空空括号
sql = replaceSqlWithRegex(sql, "(where\\s+)(and|or|in)(\\s+|$)", new int[]{1});
sql = replaceSqlWithRegex(sql,"(\\s+)(where|having|order\\s+by|group\\s+by)(\\s*$|\\s*\\)|\\s+union)",new int[]{3});
sql = replaceSqlWithRegex(sql, "where\\s+(group|order|$)", new int[]{1});
sql = replaceSqlWithRegex(sql, "having\\s+(order|$)", new int[]{1});
sql = replaceSqlWithRegex(sql, "group\\s+by\\s+(order|$)", new int[]{1});
sql = sql.replaceAll("\\s{2,}", " ");// 删除多余空格
return new String(sql);
}
// sql添加空格
public static String addSqlBlank(String sql) throws Exception {
sql = replaceSqlWithRegex(sql, "(\\s+)(=|<|>|!|-|\\+|\\*|/|\\()(\\s+)",new int[]{2});// 去除运算符附近空格
sql = replaceSqlWithRegex(sql, "(\\s+)(=|<|>|!|-|\\+|\\*|/)(\\s*)", new int[]{2});// 去除运算符附近空格
sql = replaceSqlWithRegex(sql, "(\\s*)(=|<|>|!|-|\\+|\\*|/)(\\s+)", new int[]{2});// 去除运算符附近空格
sql = replaceSqlWithRegex(sql, "(where\\s+)(,|and|or|in)(\\s+|$)", new int[]{1});// 清空where后面直接带连接符
sql = cleanSqlComment(sql);
sql = lowerCaseOracleKeyWord(sql);
sql = sql.replaceAll("\\s*:\\s*", ":");// 去除:附近空格
sql = sql.replaceAll("\\*from", "* from ");// from前面添加空格
sql = sql.replaceAll("select", "select ");// from前面添加空格
sql = sql.replaceAll("\\)", " )");// )前面添加空格
sql = sql.replaceAll("(?<!and|or)\\s*\\(\\s*", "(");// 去除(附近空格
sql = sql.replaceAll("\\s+in\\s*\\(", " in ( ");// in(添加空格
sql = sql.replaceAll("\\s+having\\s*\\(", " having( ");// having(添加空格
sql = sql.replaceAll(",", " , ");// ,后面添加空格
// 删除不匹配的注释
sql = sql.replace("/*", " ");
sql = sql.replace("*/", " ");
return new String(sql);
}
public static String replaceSqlWithRegex(String sql, String regex,
int[] indexs) throws Exception {
Pattern p = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
Arrays.sort(indexs);
// 添加循环匹配失败检测
int isFail = 0;
while (true) {
while (m.find()) {
isFail++;
if (isFail > Byte.MAX_VALUE) {
throw new Exception("检测到匹配次数过多，请优化匹配表达式");
}
String tmp = new String();
for (int i = 0; i < indexs.length; i++) {
tmp += m.group(indexs[i]);
}
sql = sql.replace(m.group(), tmp);
}
m = p.matcher(sql);
if (!m.find()) {
break;
}
m.reset();
}
return new String(sql);
}
// 数据库关键字小写
public static String lowerCaseOracleKeyWord(String sql) throws Exception {
String regexStr = "(?<=\\s+|\\(|\\)|^)(select|as|from|where|and|or|in|exists|extract|to_date|to_char|not|trunc|group|by|having|order)(?=\\s+|\\(|\\)|$)";
sql = replaceStrByAppend(regexStr, sql, true);
return sql;
}
// 大小写字符串
public static String replaceStrByAppend(String regEx, String sql,
boolean isLower) {
Pattern pattern = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(sql);
StringBuffer sb = new StringBuffer();
int lastEnd = 0;// 上一次匹配结束位置
while (matcher.find()) {
sb.append(sql.substring(lastEnd, matcher.start()));
if (isLower) {
sb.append(sql.substring(matcher.start(), matcher.end())
.toLowerCase());
} else {
sb.append(sql.substring(matcher.start(), matcher.end()));
}
lastEnd = matcher.end();
}
if (lastEnd < sql.length()) {
sb.append(sql.substring(lastEnd));
}
return sb.toString();
}
public static List<String> splitSqlByUnion(String sql){
List<String> resultList=new ArrayList<String>();
if(sql.indexOf(" union ")==-1){
resultList.add(sql);
}else{
int position=sql.indexOf(" union ");
while(position!=-1){
String tmpStr=sql.substring(0,sql.indexOf(" union ")+7);
resultList.add(tmpStr);
sql=sql.substring(sql.indexOf(" union ")+7);
position=sql.indexOf(" union ");
}
if(sql!=null){
resultList.add(sql);
}
}
return resultList;
}
// 字符串分割保留分隔符
public static String[] splitByStrNormal(String str, String split) {
Pattern p = Pattern.compile(split);
Matcher m = p.matcher(str);
String[] words = p.split(str);
String[] strResult = new String[words.length];
strResult[0] = words[0];
if (words.length > 0) {
int count = 1;
while (count < words.length) {
if (m.find()) {
strResult[count] = new String(m.group() + words[count]);
}
count++;
}
}
return strResult;
}
// 清除sql内的注释
public static String cleanSqlComment(String sql) {
if (sql.indexOf("/*") == -1) {
return sql;
}
StringBuilder sb = new StringBuilder((int) (sql.length() * 1.5));
int cursor = 0, start = -1, end = -1;
for (; (start = sql.indexOf("/*", cursor)) != -1
&& (end = sql.indexOf("*/", start)) != -1;) {
sb.append(' ').append(sql.substring(cursor, start));
cursor = end + 2;
}
sb.append(sql.substring(cursor, sql.length()));
return sb.toString();
}
// 初步检查sql是否正确 规则1)select前面有其他字符,2)sql括号数量不匹配 3)sql中注释不匹配
public static String checkSql(String sql) {
StringBuffer errorInfo = new StringBuffer();
sql = sql.trim().toLowerCase();
if (!sql.startsWith("select")) {
errorInfo.append(" select开始前有其他字符");
}
int leftBracket = sql.length() - sql.replaceAll("\\(", "").length(), rightBracket = sql
.length() - sql.replaceAll("\\)", "").length();
if (leftBracket != rightBracket) {
errorInfo.append(" 括号不匹配");
}
sql = cleanSqlComment(sql);
if (sql.indexOf("/*") != -1 || sql.indexOf("*/") != -1) {
errorInfo.append(" sql中存在多余的注释");
}
return errorInfo.toString();
}
public static StringBuffer map2StringBuffer(Map<String, String> map) {
StringBuffer result = new StringBuffer();
for (Entry<String, String> entry : map.entrySet()) {
result.append(entry.getKey()).append("=").append(entry.getValue())
.append(",");
}
if (result.length() > 0) {
result.deleteCharAt(result.length() - 1);
}
return result;
}
}替换前:
select rownum, bb.*
from (select :a,
:b + 4,
4 + :c,
5 + :b,
to_char(:c, 'yyy-mm-dd'),
to_date(:v, 'yyyy-mm-dd')
from a
where a.order_id = :b
and b in (:b)
and c in (:b, :l)
and d in (:l, 0)
and e in (0, :l)
and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd')
and b.prod_spec_id = :MODEL_ID
and extract(month from b.done_date) =
extract(month from to_date(:b, ' yyyy - mm '))
and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date)
and a.order_type = 80
and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd')
and extract(month from b.done_date) =
extract(month from to_date(:b, 'yyyy-mm'))
and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date)
and extract(month from b.done_date) =
extract(month from to_date(:b, ' yyyy - mm '))
union
select extract(month from to_date(:b, ' yyyy - mm ')),
to_char(:v, 'yyy-m-dd'),
to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd')
from b
group by a
having extract(month
from b.done_date) = :b and b = :b
union all
select extract(month from to_date(:b, ' yyyy - mm ')),
to_char(:v, 'yyy-m-dd'),
to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd')
from b
group by a
having(extract(month
from b.done_date) = :b and b = :b) and b = :k) bb
group by aa, bb desc
having(aa = :k and bb > :b + 5)
替换后:
替换结果为=select rownum , bb.* from(select ' ' , '0'+4 , 4+'0' , 5+'0' , ' ' , ' ' from a where d in ( 0 ) and e in ( 0 ) and a.order_type=80 union select ' ' , ' ' , ' ' from b group by a union all select ' ' , ' ' , ' ' from b group by a ) bb group by aa , bb desc having( bb>'0'+5 )
select rownum, bb.*
from (select ' ', '0' + 4, 4 + '0', 5 + '0', ' ', ' '
from a
where d in (0)
and e in (0)
and a.order_type = 80
union
select ' ', ' ', ' '
from b
group by a
union all
select ' ', ' ', ' ' from b group by a) bb
group by aa, bb desc
having(bb > '0' + 5)
全文完。
扫一扫
2617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
