var sql = "select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
}
}
}
};
var sql = "select id AS bid,t.NAME testName from test t";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "id",
},
Alias = new SqlIdentifierExpression()
{
Value = "bid",
},
},
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "testName",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
var sql = "select 1+2 from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlNumberExpression()
{
Value = 1M,
},
Operator = SqlBinaryOperator.Add,
Right = new SqlNumberExpression()
{
Value = 2M,
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
var sql = "select ''' ''',3,true FROM test";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlStringExpression()
{
Value = "' '"
},
},
new SqlSelectItemExpression()
{
Body = new SqlNumberExpression()
{
Value = 3M,
},
},
new SqlSelectItemExpression()
{
Body = new SqlBoolExpression()
{
Value = true
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
var sql = "select LOWER(name) FROM test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "LOWER",
},
Arguments = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
var sql = "SELECT t.*, ROW_NUMBER() OVER ( PARTITION BY t.ID ORDER BY t.NAME,t.ID) as rnum FROM TEST t";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "*",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ROW_NUMBER",
},
Over = new SqlOverExpression()
{
PartitionBy = new SqlPartitionByExpression()
{
Items = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ID",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ID",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "rnum",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
var sql = "select name,PERCENTILE_CONT(0.5) within group(order by \"number\") from TEST5 group by name";
var sqlAst = DbUtils.Parse(sql, DbType.Pgsql);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "PERCENTILE_CONT",
},
WithinGroup = new SqlWithinGroupExpression()
{
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "number",
LeftQualifiers = "\"",
RightQualifiers = "\"",
},
},
},
},
},
Arguments = new List<SqlExpression>()
{
new SqlNumberExpression()
{
Value = 0.5M,
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST5",
},
},
GroupBy = new SqlGroupByExpression()
{
Items = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
};
within group子句，即WithinGroup字段，他的值为一个SqlWithinGroupExpression表达式，SqlWithinGroupExpression又包含了OrderBy排序子句，这里根据number字段排序
1.1.5 查询列为子查询的情况
var sql = "select c.*, (select a.name as province_name from portal_area a where a.id = c.province_id) as province_name, (select a.name as city_name from portal_area a where a.id = c.city_id) as city_name, (CASE WHEN c.area_id IS NULL THEN NULL ELSE (select a.name as area_name from portal_area a where a.id = c.area_id) END )as area_name from portal.portal_company c";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "*",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
new SqlSelectItemExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "province_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "province_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "province_name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "city_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "city_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "city_name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "area_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
Operator = SqlBinaryOperator.Is,
Right = new SqlNullExpression()
},
Value = new SqlNullExpression()
},
},
Else = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "area_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "area_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "area_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_company",
},
Schema = new SqlIdentifierExpression()
{
Value = "portal",
},
Alias = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
};
var sql = "SELECT * FROM test WHERE ID =1";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "ID",
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlNumberExpression()
{
Value = 1M,
},
},
},
};
var sql = "SELECT * FROM test WHERE ID BETWEEN 1 AND 2";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
Where = new SqlBetweenAndExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "ID",
},
Begin = new SqlNumberExpression()
{
Value = 1M,
},
End = new SqlNumberExpression()
{
Value = 2M,
},
},
},
};
var sql = "select * from test rd where rd.name is null";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "rd",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "rd",
},
},
Operator = SqlBinaryOperator.Is,
Right = new SqlNullExpression()
},
},
};
is null/is not null子句主要体现在二元表达式里，Operator字段为Is/IsNot，right字段为SqlNullExpression，即null表达式，代表值为null
1.2.4 exists/not exists子句
var sql = "select * from TEST t where EXISTS(select * from TEST2 t2)";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlExistsExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t2",
},
},
},
},
},
},
};
exists/not exists子句,主要体现为SqlExistsExpression表达式，
主体，即body字段，本例子中值为一个SqlSelectExpression表达式
取反部分，即IsNot字段，如果是not exists，则IsNot=true
1.2.5 like/not like子句
var sql = "SELECT * from TEST t WHERE name LIKE '%a%'";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name",
},
Operator = SqlBinaryOperator.Like,
Right = new SqlStringExpression()
{
Value = "%a%"
},
},
},
};
var sql = "select * from customer c where c.Age >all(select o.Quantity from orderdetail o)";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "customer",
},
Alias = new SqlIdentifierExpression()
{
Value = "c",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Age",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
Operator = SqlBinaryOperator.GreaterThen,
Right = new SqlAllExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Quantity",
},
Table = new SqlIdentifierExpression()
{
Value = "o",
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "orderdetail",
},
Alias = new SqlIdentifierExpression()
{
Value = "o",
},
},
},
},
},
},
},
};
var sql = "SELECT * from TEST t WHERE t.NAME IN ('a','b','c')";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlInExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
TargetList = new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a"
},
new SqlStringExpression()
{
Value = "b"
},
new SqlStringExpression()
{
Value = "c"
},
},
},
},
};
var sql = "select * from TEST5 WHERE NAME IN (SELECT NAME FROM TEST3)";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST5",
},
},
Where = new SqlInExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "NAME",
},
SubQuery = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "NAME",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST3",
},
},
},
},
},
},
};
在这里的SqlInExpression表达式中，它包含了
body字段，即in的主体，在这里是SqlIdentifierExpression，值为NAME
SubQuery字段，即子查询，值为一个SqlSelectExpression
IsNot字段，如果是not in，则IsNot=true
1.2.8 case when子句
var sql = "SELECT CASE WHEN t.name='1' THEN 'a' WHEN t.name='2' THEN 'b' ELSE 'c' END AS v from TEST t";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "1"
},
},
Value = new SqlStringExpression()
{
Value = "a"
},
},
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "2"
},
},
Value = new SqlStringExpression()
{
Value = "b"
},
},
},
Else = new SqlStringExpression()
{
Value = "c"
},
},
Alias = new SqlIdentifierExpression()
{
Value = "v",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
var sql = "select case t.name when 'a' then 1 else 2 end from test t ";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlStringExpression()
{
Value = "a"
},
Value = new SqlNumberExpression()
{
Value = 1M,
},
},
},
Else = new SqlNumberExpression()
{
Value = 2M,
},
Value = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
var sql = "select * from TEST t WHERE not t.NAME ='abc'";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlNotExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "abc"
},
},
},
},
};
var sql = "select * from TEST t WHERE not t.NAME =:name";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlNotExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlVariableExpression()
{
Name = "name",
Prefix = ":",
},
},
},
},
};
变量子句,主要体现在SqlVariableExpression表达式里，它包括以下部分:
变量名，即字段Name,这里值为name
变量前缀，这里值为:
1.3 From数据源
在sql中，From关键字后面有多种形式来指定数据源。主要有以下几种
1.3.1 表名或者视图
select*from test
这个解析结果上面已经演示了。
1.3.2 子查询（子表）
var sql = "select * from (select * from test) t";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlSelectExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
},
},
};
var sql = "select t1.id from test t1 left join test2 t2 on t1.id=t2.id right join test3 t3 on t2.id=t3.id";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t1",
},
},
},
},
From = new SqlJoinTableExpression()
{
Left = new SqlJoinTableExpression()
{
Left = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t1",
},
},
JoinType = SqlJoinType.LeftJoin,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t2",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t1",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t2",
},
},
},
},
JoinType = SqlJoinType.RightJoin,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test3",
},
Alias = new SqlIdentifierExpression()
{
Value = "t3",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t2",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t3",
},
},
},
},
},
};
var sql = "with c1 as (select name from test t) , c2(name) AS (SELECT name FROM TEST2 t3 ) select *from c1 JOIN c2 ON c1.name=c2.name";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
WithSubQuerys = new List<SqlWithSubQueryExpression>()
{
new SqlWithSubQueryExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "c1",
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
new SqlWithSubQueryExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "c2",
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t3",
},
},
},
},
Columns = new List<SqlIdentifierExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlJoinTableExpression()
{
Left = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "c1",
},
},
JoinType = SqlJoinType.InnerJoin,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "c2",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "c1",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "c2",
},
},
},
},
},
};
var sql = "SELECT * FROM TABLE(splitstr('a;b',';'))";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlReferenceTableExpression()
{
FunctionCall = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TABLE",
},
Arguments = new List<SqlExpression>()
{
new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "splitstr",
},
Arguments = new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a;b"
},
new SqlStringExpression()
{
Value = ";"
},
},
},
},
},
}
},
};
var sql = "select fa.FlowId from FlowActivity fa order by fa.FlowId desc,fa.Id asc";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowActivity"
},
Alias = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body =
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderByType = SqlOrderByType.Desc
},
new SqlOrderByItemExpression()
{
Body =
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderByType = SqlOrderByType.Asc
},
},
},
},
};
var sql = "select fa.FlowId from FlowActivity fa group by fa.FlowId,fa.Id HAVING count(fa.Id)>1";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowActivity"
},
Alias = new SqlIdentifierExpression()
{
Value = "fa"
},
},
GroupBy = new SqlGroupByExpression()
{
Items = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
Having = new SqlBinaryExpression()
{
Left = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "count"
},
Arguments = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
Operator = SqlBinaryOperator.GreaterThen,
Right = new SqlNumberExpression()
{
Value = 1M
},
},
},
},
};
var sql = "select * from test t limit 1,5";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 1M,
},
RowCount = new SqlNumberExpression()
{
Value = 5M,
},
},
},
};
Limit分页子句，值为SqlLimitExpression表达式，他的内容如下
每页数量，即RowCount字段，这本例子中，值为5
跳过数量，即Offset字段,本例子中，值为1
1.5.2 oracle
var sql = "SELECT * FROM TEST3 t ORDER BY t.NAME DESC FETCH FIRST 2 rows ONLY";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
Name = new SqlIdentifierExpression()
{
Value = "TEST3"
}
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
OrderByType = SqlOrderByType.Desc,
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression() { Value = "NAME" },
Table = new SqlIdentifierExpression()
{
Value = "t"
}
}
}
}
},
Limit = new SqlLimitExpression()
{
RowCount = new SqlNumberExpression()
{
Value = 2
}
}
}
};
1.5.3 pgsql
var sql = "select * from test5 t order by t.name limit 1 offset 10;";
var sqlAst = DbUtils.Parse(sql, DbType.Pgsql);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test5",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 10M,
},
RowCount = new SqlNumberExpression()
{
Value = 1M,
},
},
},
};
var sql = "select * from test t order by t.name OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
Name = new SqlIdentifierExpression()
{
Value = "test"
}
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression() { Value = "name" },
Table = new SqlIdentifierExpression()
{
Value = "t"
}
}
}
}
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 5
},
RowCount = new SqlNumberExpression()
{
Value = 10
}
}
}
};
1.6 ConnectBy层次查询语句（oracle专用）
var sql = "SELECT EMPLOYEEID , MANAGERID , LEVEL FROM EMPLOYEE e START WITH MANAGERID IS NULL CONNECT BY NOCYCLE PRIOR EMPLOYEEID = MANAGERID ORDER SIBLINGS BY EMPLOYEEID ";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
},
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
},
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "LEVEL",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "EMPLOYEE",
},
Alias = new SqlIdentifierExpression()
{
Value = "e",
},
},
ConnectBy = new SqlConnectByExpression()
{
StartWith = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
Operator = SqlBinaryOperator.Is,
Right = new SqlNullExpression()
},
Body = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
},
IsNocycle = true,
IsPrior = true,
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
},
},
IsSiblings = true,
},
},
},
};
var sql = "SELECT name into test14 from TEST as t ";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name"
},
},
},
Into = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test14"
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST"
},
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
},
},
};
into子句，在本例子中值为SqlTableExpression，即into到某张表里。
2. Insert插入语句
2.1 插入单个值
var sql = "insert into test11(name,id) values('a1','a2')";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下：
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
new SqlIdentifierExpression()
{
Value = "id"
},
},
ValuesList = new List<List<SqlExpression>>()
{
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a1"
},
new SqlStringExpression()
{
Value = "a2"
},
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test11"
},
},
};
var sql = "insert into test11(name,id) values('a1','a2'),('a3','a4')";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下：
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
new SqlIdentifierExpression()
{
Value = "id"
},
},
ValuesList = new List<List<SqlExpression>>()
{
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a1"
},
new SqlStringExpression()
{
Value = "a2"
},
},
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a3"
},
new SqlStringExpression()
{
Value = "a4"
},
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test11"
},
},
};
var sql = "INSERT INTO TEST2(name) SELECT name AS name2 FROM TEST t";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下：
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2"
},
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name"
},
Alias = new SqlIdentifierExpression()
{
Value = "name2"
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST"
},
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
},
},
},
};
var sql = "update test set name ='4',d='2024-11-22 08:19:47.243' where name ='1'";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下：
var expect = new SqlUpdateExpression()
{
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "1"
},
},
Items = new List<SqlExpression>()
{
new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "4"
},
},
new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "d"
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "2024-11-22 08:19:47.243"
},
},
},
};
var sql = "delete from test where name=4";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下：
var expect = new SqlDeleteExpression()
{
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlNumberExpression()
{
Value = 4M
},
},
};
var sql = @"select *--abc from test lbu WHERE a ='1'--aaaaaa
FROM test";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
如上例子，单行注释被正确忽视，解析正确。
5.2 多行注释
var sql = @"/*这
是
顶部*/
select *--abc
FROM test/*这
是
底部*/";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
如上例子，多行注释被正确忽视，解析正确。
6. 如何解析ast抽象语法树
当我们通过
var sql = @"select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
var sql = @"select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
var unitTestAstVisitor = new UnitTestAstVisitor();
sqlAst.Accept(unitTestAstVisitor);
var result = unitTestAstVisitor.GetResult();
其中的result就是解析抽象语法树生成的字符串，如下：
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
var sql = @"select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
if (sqlAst is SqlSelectExpression sqlSelectExpression && sqlSelectExpression.Query is SqlSelectQueryExpression sqlSelectQueryExpression)
{
sqlSelectQueryExpression.Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Table = new SqlIdentifierExpression()
{
Value = "test"
},
Name = new SqlIdentifierExpression()
{
Value = "name"
}
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "a"
}
};
}
var sqlGenerationAstVisitor = new SqlGenerationAstVisitor(DbType.Oracle);
sqlAst.Accept(sqlGenerationAstVisitor);
var newSql = sqlGenerationAstVisitor.GetResult();
//如果是手机访问,设置手机缓存目录
if(isMobile()){ KaTeX parse error: Expected ‘EOF’, got ‘}’ at position 37: …] = ‘mobile/’; }̲elseif(isset(_SESSION[‘theme_path’])){
unset($_SESSION[‘theme_path’]);
}
//判断是否是手机访问
function isMobile(){
if(isset(KaTeX parse error: Double subscript at position 16: _SERVER[‘HTTP_X_̲WAP_PROFILE’]))…_SERVER[‘HTTP_VIA’]))return stristr(S E R V E R [ ′ H T T P V I A ′ ] , " w a p " ) ? t r u e : f a l s e ; i f ( i s s e t ( _SERVER['HTTP_VIA'], "wap") ? true : false; if(isset(SERVER[′HTTPVIA′],“wap“)?true:false;if(isset(_SERVER[‘HTTP_USER_AGENT’])){$clientkeywords = array (‘nokia’,‘sony’,‘ericsson’,‘mot’,‘samsung’,‘htc’,‘sgh’,‘lg’,‘sharp’,‘sie-’,‘philips’,‘panasonic’,‘alcatel’,‘lenovo’,‘iphone’,‘ipod’,‘blackberry’,‘meizu’,‘Android’,‘netfront’,‘symbian’,‘ucweb’,‘windowsce’,‘palm’,‘operamini’,‘operamobi’,‘openwave’,‘nexusone’,‘cldc’,‘midp’,‘wap’,‘mobile’);if (preg_match(“/(” . implode(’|’, c l i e n t k e y w o r d s ) . " ) / i " , s t r t o l o w e r ( clientkeywords) . ")/i", strtolower(clientkeywords).“)/i“,strtolower(_SERVER[‘HTTP_USER_AGENT’])))return true;}
if(isset(KaTeX parse error: Expected ‘}’, got ‘EOF’ at end of input: …])){if((strpos(_SERVER[‘HTTP_ACCEPT’], ‘vnd.wap.wml’) !== false) && (strpos(S E R V E R [ ′ H T T P A C C E P T ′ ] , ′ t e x t / h t m l ′ ) = = = f a l s e ∣ ∣ ( s t r p o s ( _SERVER['HTTP_ACCEPT'], 'text/html') === false || (strpos(SERVER[′HTTPACCEPT′],′text/html′)===false∣∣(strpos(_SERVER[‘HTTP_ACCEPT’], ‘vnd.wap.wml’) < strpos($_SERVER[‘HTTP_ACCEPT’], ‘text/html’))))return true;}
return false;
}
public class WorkOrderService
{
private static List<WorkOrder> workOrders = new List<WorkOrder>
{
new WorkOrder { WorkOrderName = "9050100", ProductName = "A5E900100", ProductVersion = "001 / AB", Quantity = 100, Status = "Ready" },
new WorkOrder { WorkOrderName = "9050101", ProductName = "A5E900101", ProductVersion = "001 / AB", Quantity = 200, Status = "Ready" },
new WorkOrder { WorkOrderName = "9050102", ProductName = "A5E900102", ProductVersion = "001 / AB", Quantity = 300, Status = "InProcess" },
new WorkOrder { WorkOrderName = "9050103", ProductName = "A5E900103", ProductVersion = "001 / AB", Quantity = 400, Status = "InProcess" },
new WorkOrder { WorkOrderName = "9050104", ProductName = "A5E900104", ProductVersion = "001 / AB", Quantity = 500, Status = "Completed" }
};
public WorkOrder GetWorkOrderInfo(string orderName)
{
return workOrders.Find(o => o.WorkOrderName == orderName);
}
public string UpdateWorkOrderStatus(string orderName, string newStatus)
{
var workOrder = this.GetWorkOrderInfo(orderName);
if (workOrder == null)
return "Operate Failed : The work order is not existing!";
// Update status if it is valid
workOrder.Status = newStatus;
return "Operate Succeed!";
}
public string ReduceWorkOrderQuantity(string orderName, int newQuantity)
{
var workOrder = this.GetWorkOrderInfo(orderName);
if (workOrder == null)
return "Operate Failed : The work order is not existing!";
// Some business checking logic like this
if (workOrder.Status == "Completed")
return "Operate Failed : The work order is completed, can not be reduced!";
if (newQuantity <= 1 || newQuantity >= workOrder.Quantity)
return "Operate Failed : The new quantity is invalid!";
// Update quantity if it is valid
workOrder.Quantity = newQuantity;
return "Operate Succeed!";
}
}
（3）Form
appsetting.json
{
"LLM_API_PROVIDER": "ZhiPuAI",
"LLM_API_MODEL": "glm-4",
"LLM_API_BASE_URL": "https://open.bigmodel.cn",
"LLM_API_KEY": "***********" // Update this value to yours
}
AgentForm.cs
初始化Kernel
private Kernel _kernel = null;
private OpenAIPromptExecutionSettings _settings = null;
private IChatCompletionService _chatCompletion = null;
private ChatHistory _chatHistory = null;
private void ChatForm_Load(object sender, EventArgs e)
{
var configuration = new ConfigurationBuilder().AddJsonFile($"appsettings.ZhiPu.json");
var config = configuration.Build();
var openAiConfiguration = new OpenAiConfiguration(
config.GetSection("LLM_API_PROVIDER").Value,
config.GetSection("LLM_API_MODEL").Value,
config.GetSection("LLM_API_BASE_URL").Value,
config.GetSection("LLM_API_KEY").Value);
var openAiClient = new HttpClient(new CustomLlmApiHandler(openAiConfiguration.Provider, openAiConfiguration.EndPoint));
_kernel = Kernel.CreateBuilder()
.AddOpenAIChatCompletion(openAiConfiguration.ModelId, openAiConfiguration.ApiKey, httpClient: openAiClient)
.Build();
_chatCompletion = _kernel.GetRequiredService<IChatCompletionService>();
_chatHistory = new ChatHistory();
_chatHistory.AddSystemMessage("You are one WorkOrder Assistant.");
}
注册Functions
_kernel.Plugins.Add(KernelPluginFactory.CreateFromFunctions("WorkOrderHelperPlugin",
new List<KernelFunction>
{
_kernel.CreateFunctionFromMethod((string orderName) =>
{
var workOrderRepository = new WorkOrderService();
return workOrderRepository.GetWorkOrderInfo(orderName);
}, "GetWorkOrderInfo", "Get WorkOrder's Detail Information"),
_kernel.CreateFunctionFromMethod((string orderName, int newQuantity) =>
{
var workOrderRepository = new WorkOrderService();
return workOrderRepository.ReduceWorkOrderQuantity(orderName, newQuantity);
}, "ReduceWorkOrderQuantity", "Reduce WorkOrder's Quantity to new Quantity"),
_kernel.CreateFunctionFromMethod((string orderName, string newStatus) =>
{
var workOrderRepository = new WorkOrderService();
return workOrderRepository.UpdateWorkOrderStatus(orderName, newStatus);
}, "UpdateWorkOrderStatus", "Update WorkOrder's Status to new Status")
}
));
–C:\inetpub\SlowPage.sql
Select Top 20
LogRow as [Line Number],
date as [Date],
time as [Time],
c-ip as [Client-IP],
s-ip as [Server IP],
s-port as [Server Port],
cs-method as [Request Verb],
cs-uri-stem as [Request URI],
sc-bytes as [Bytes sent],
sc-status as [Status],
sc-substatus as [Sub-status],
sc-win32-status as [Win 32 Status],
time-taken as [Time Taken]
From
C:\inetpub\logs\LogFiles\W3SVC2\u_ex*.log
Order by time-taken desc
Mikel
<view class=”u-menu-wrap”><scroll-view scroll-y scroll-with-animation class=”u-tab-view menu-scroll-view” :scroll-top=”scrollTop”><view v-for=”(item,index) in tabbar” :key=”index” class=”u-tab-item” :class=”[current==index ? ‘u-tab-item-active’ : ”]”:data-current=”index” @tap.stop=”swichMenu(index)”><text class=”u-line-1″>{{item.name}}</text></view></scroll-view><block v-for=”(item,index) in tabbar” :key=”index”><scroll-view scroll-y class=”right-box” v-if=”current==index”><view class=”page-view”><view class=”class-item”><view class=”item-title”><text>{{item.name}}</text></view><view class=”item-container”><view class=”thumb-box” v-for=”(item1, index1) in item.foods” :key=”index1″><image class=”item-menu-image” :src=”item1.icon” mode=””></image><view class=”item-menu-name”>{{item1.name}}</view></view></view></view></view></scroll-view></block></view><script>import classifyData from “@/common/classify.data.js”;//是本地模拟的数据export default {data() {return {tabbar: classifyData,scrollTop: 0, //tab标题的滚动条位置current: 0, // 预设当前项的值menuHeight: 0, // 左边菜单的高度menuItemHeight: 0, // 左边菜单item的高度}},methods: {// 点击左边的栏目切换async swichMenu(index) {if(index == this.current) return ;this.current = index;// 如果为0，意味着尚未初始化if(this.menuHeight == 0 || this.menuItemHeight == 0) {await this.getElRect(‘menu-scroll-view’, ‘menuHeight’);await this.getElRect(‘u-tab-item’, ‘menuItemHeight’);}// 将菜单菜单活动item垂直居中this.scrollTop = index * this.menuItemHeight + this.menuItemHeight / 2 – this.menuHeight / 2;},// 获取一个目标元素的高度getElRect(elClass, dataVal) {new Promise((resolve, reject) => {const query = uni.createSelectorQuery().in(this);query.select(‘.’ + elClass).fields({size: true},res => {// 如果节点尚未生成，res值为null，循环调用执行if(!res) {setTimeout(() => {this.getElRect(elClass);}, 10);return ;}this[dataVal] = res.height;}).exec();})}}}</script><style lang=”scss” scoped>.u-wrap {height: calc(100vh);/* #ifdef H5 */height: calc(100vh – var(–window-top));/* #endif */display: flex;flex-direction: column;}.u-search-box {padding: 18rpx 30rpx;}.u-menu-wrap {flex: 1;display: flex;overflow: hidden;}.u-search-inner {background-color: rgb(234, 234, 234);border-radius: 100rpx;display: flex;align-items: center;padding: 10rpx 16rpx;}.u-search-text {font-size: 26rpx;color: $u-tips-color;margin-left: 10rpx;}.u-tab-view {width: 200rpx;height: 100%;}.u-tab-item {height: 110rpx;background: #f6f6f6;box-sizing: border-box;display: flex;align-items: center;justify-content: center;font-size: 26rpx;color: #444;font-weight: 400;line-height: 1;}.u-tab-item-active {position: relative;color: #000;font-size: 30rpx;font-weight: 600;background: #fff;}.u-tab-item-active::before {content: “”;position: absolute;border-left: 4px solid $u-type-primary;height: 32rpx;left: 0;top: 39rpx;}.u-tab-view {height: 100%;}.right-box {background-color: rgb(250, 250, 250);}.page-view {padding: 16rpx;}.class-item {margin-bottom: 30rpx;background-color: #fff;padding: 16rpx;border-radius: 8rpx;}.item-title {font-size: 26rpx;color: $u-main-color;font-weight: bold;}.item-menu-name {font-weight: normal;font-size: 24rpx;color: $u-main-color;}.item-container {display: flex;flex-wrap: wrap;}.thumb-box {width: 33.333333%;display: flex;align-items: center;justify-content: center;flex-direction: column;margin-top: 20rpx;}.item-menu-image {width: 120rpx;height: 120rpx;}</style>
