跳至主要內容

表达式函数

nicye大约 14 分钟约 4305 字

表达式函数

这是 FreeSql 非常特色的功能之一,深入细化函数解析,所支持的类型基本都可以使用对应的表达式函数,例如 日期、字符串、IN查询、数组(PostgreSQL的数组)、字典(PostgreSQL HStore)等等。

动态Lambda表达式

Expression<Func<T, bool>> where = null;
where = where.And(b => b.num > 0);
where = where.Or(b => b.num > 0);
fsql.Select<T>().Where(where)
  • 多表
Expression<Func<HzyTuple<T1, T2, T3, T4, T5, T6>, bool>> where = null;
where = where.Or(a => a.t6.Id > 0);
fsql.Select<T1, T2, T3, T4, T5, T6>().Where(where)

In查询

var t1 = fsql.Select<T>()
  .Where(a => new[] { 1, 2, 3 }.Contains(a.Id))
  .ToList();
//SELECT .. FROM ..
//WHERE (a.`Id` in (1,2,3))

已优化,防止 where in 元素多过的 SQL 错误,如:

[Err] ORA-01795: maximum number of expressions in a list a 1000

原来:where id in (1..1333)

现在:where id in (1..500) or id in (501..1000) or id in (1001..1333)

In多列查询

//元组集合
vae lst = new List<(Guid, DateTime)>();
lst.Add((Guid.NewGuid(), DateTime.Now));
lst.Add((Guid.NewGuid(), DateTime.Now));

var t2 = fsql.Select<T>()
  .Where(a => lst.Contains(a.Id, a.ct1))
  .ToList();
//SELECT .. FROM ..
//WHERE (a."Id" = '685ee1f6-bdf6-4719-a291-c709b8a1378f' AND a."ct1" = '2019-12-07 23:55:27' OR 
//a."Id" = '5ecd838a-06a0-4c81-be43-1e77633b7404' AND a."ct1" = '2019-12-07 23:55:27')

v3.2.650 使用 .Where(a => list.Any(b => b.Item1 == a. Id && b.Item2 == a. ct1))

WHERE (id, code) in ((1,'code1'), (2,'code2')) 实现代码:In多列查询,表达式自定义实现

In子表

fsql.Select<Topic>()
  .Where(a => fsql.Select<Topic>().As("b").ToList(b => b.Id).Contains(a.Id))
  .ToList();
//SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
//FROM `Topic` a
//WHERE (((a.`Id`) in (SELECT b.`Id`
//    FROM `Topic` b)))

Exists子表

fsql.Select<Topic>()
  .Where(a => fsql.Select<Topic>().As("b").Where(b => b.Id == a.Id).Any())
  .ToList();
//SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
//FROM `Topic` a
//WHERE (exists(SELECT 1
//    FROM `Topic` b
//    WHERE (b.`Id` = a.`Id`)
//    limit 0,1))

提示:由于子查询的实体类与上层相同,使用 As("b") 指明别名,以便区分

查找今天创建的数据

fsql.Select<T>()
  .Where(a => a.CreateTime.Date == DateTime.Today)
  .ToList();
//这行代码说明 FreeSql 表达式解析强大,不是所有 ORM 都支持

fsql.Select<T>()
  .Where(a => a.CreateTime.Between(DateTime.Today, DateTime.Today.AddDays(1)))
  .ToList();
//正常用法应该是这样

SqlServer nvarchar/varchar 已兼容表达式解析,分别解析为:N'' 和 '',优化索引执行计划;

日期格式化

fsql.Select<T>()
  .First(a => a.CreateTime.ToString("HH:mm:ss"));
// SELECT date_format(a.`CreateTime`, '%H:%i:%s') as1 
// FROM `xxx` a 
// limit 0,1

v1.5.0 支持了常用 c# 日期格式化,yyyy MM dd HH mm ss yy M d H hh h m s tt t

tt t 为 AM PM

开窗函数

fsql.Select<T1, T2>()
  .InnerJoin((a, b) => b.Id == a.Id)
  .ToList((a, b) => new
  {
    Id = a.Id,
    EdiId = b.Id,
    over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue()
  });

v1.6.0 利用自定义解析功能,增加 SqlExt.Rank().Over().PartitionBy(...)、MySql group_concat 常用函数,欢迎 PR 补充

FreeSql 默认集成了 SqlExt.cs 扩展解析方法:

lambdasql说明
SqlExt.IsNull(id, 0)isnull/ifnull/coalesce/nvl兼容各大数据库
SqlExt.DistinctCount(id)count(distinct id)
SqlExt.GreaterThan>大于
SqlExt.GreaterThanOrEqual>=大于或等于
SqlExt.LessThan<小于
SqlExt.LessThanOrEqual<=小于
SqlExt.EqualIsNullIS NULL是否为 NULL
SqlExt.Case(字典)case when .. end根据字典 case
SqlExt.GroupConcatgroup_concat(distinct .. order by .. separator ..)MySql
SqlExt.FindInSetfind_in_set(str, strlist)MySql
SqlExt.StringAggstring_agg(.., ..)PostgreSQL
SqlExt.Rank().Over().PartitionBy().ToValue()rank() over(partition by xx)开窗函数
SqlExt.DenseRank().Over().PartitionBy().ToValue()dense_rank() over(partition by xx)
SqlExt.Count(id).Over().PartitionBy().ToValue()count(id) over(partition by xx)
SqlExt.Sum(id).Over().PartitionBy().ToValue()sum(id) over(partition by xx)
SqlExt.Avg(id).Over().PartitionBy().ToValue()avg(id) over(partition by xx)
SqlExt.Max(id).Over().PartitionBy().ToValue()max(id) over(partition by xx)
SqlExt.Min(id).Over().PartitionBy().ToValue()min(id) over(partition by xx)
SqlExt.RowNumber(id).Over().PartitionBy().ToValue()row_number(id) over(partition by xx)

以上是利用《自定义解析》实现的功能

子表Join

v1.8.0+ string.Join + ToList 实现将子查询的多行结果,拼接为一个字符串,如:"1,2,3,4"

fsql.Select<Topic>().ToList(a => new {
  id = a.Id,
  concat = string.Join(",", fsql.Select<StringJoin01>().ToList(b => b.Id))
});
//SELECT a.`Id`, (SELECT group_concat(b.`Id` separator ',') 
//    FROM `StringJoin01` b) 
//FROM `Topic` a

提示:子查询 string.Join + ToList 适配了 sqlserver/pgsql/oracle/mysql/sqlite/firebird/达梦/金仓/南大/翰高 #405open in new window

子表First/Count/Sum/Max/Min/Avg

fsql.Select<Category>().ToList(a => new  {
  all = a,
  first = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).First(b => b.Id),
  count = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Count(),
  sum = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Sum(b => b.Clicks),
  max = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Max(b => b.Clicks),
  min = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Min(b => b.Clicks),
  avg = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Avg(b => b.Clicks)
});

子表ToList

v3.2.650+ 以下最多执行3次 SQL

fsql.Select<Topic>().ToList(a => new
{
  all = a,
  list1 = fsql.Select<T2>().ToList(),
  list2 = fsql.Select<T2>().Where(b => b.TopicId == a.Id).ToList()
});

fsql.Select<Topic>()
  .GroupBy(a => new { a.Author })
  .WithTempQuery(a => new { Author = a.Key.Author, Count = a.Count() })
  .ToList(a => new {
    a.Author, a.Count,
    list1 = fsql.Select<T2>().ToList(),
    list2 = fsql.Select<T2>().Where(b => b.Author == a.Author).ToList()
  });

自定义解析

[ExpressionCall]
public static class DbFunc {
  //必要定义 static + ThreadLocal
  static ThreadLocal<ExpressionCallContext> context = new ThreadLocal<ExpressionCallContext>();

  public static DateTime FormatDateTime(this DateTime that, string arg1)
  {
    var up = context.Value;
    if (up.DataType == FreeSql.DataType.Sqlite) //重写内容
      up.Result = $"date_format({up.ParsedContent["that"]}, {up.ParsedContent["arg1"]})";
    return that;
  }
}

var sql1 = fsql.Select<SysModule>()
  .ToSql(a => a.CreateTime.FormatDateTime("yyyy-MM-dd"));
//SELECT date_format(a."CreateTime", 'yyyy-MM-dd') as1 
//FROM "SysModule" a

[ExpressionCall] 特性可在静态扩展类上标记,也可以在单个静态方法上标记;

ExpressionCallContext 属性类型描述
DataTypeFreeSql.DataType用于实现不同数据库的适配判断条件
ParsedContentDictionary<string, string>函数的各参数解析结果
DbParameterDbParameterthat 被参数化的对象(有可能为 null)
UserParametersList<DbParameter>可附加参数化对象
Resultstring返回表达式函数表示的 SQL 字符串

当扩展方法返回值为 string 时,其返回值也可以当作 context.Value.Result 功能

当不想解析指定参数时,使用特性 [RawValue] 标记

参数化

Where(lambda) 解析出来的默认是纯文本(已防止SQL注入),对数据库执行计划要求特别高,可以开启 lambda 参数化功能。

var fsql = new FreeSqlBuilder() //请务必定义成 Singleton 单例模式
  .UseGenerateCommandParameterWithLambda(true)
  ...

var id = 1;
fsql.Select<Song>().Where(a => a.Id == id).ToList();
//SELECT .. FROM `Song` a WHERE `Id` = @exp_0

生成的参数对象,DbType、Size、Precision、Scale 值设置默认已作优化,与实体属性定义一致。

诡异操作:

如果不希望 string 参数与实体属性的 Size 相同,可利用自定义表达式函数功能,如下:

var name = "testname";
fsql.Select<TestMySqlStringIsNullable>()
  .Where(a => a.varchar == name).ToList();

fsql.Select<TestMySqlStringIsNullable>()
  .Where(a => a.varchar == name.SetDbParameter(10)).ToList();

public class TestMySqlStringIsNullable {
  public Guid id { get; set; }

  [Column(DbType = "varchar(100)")]
  public string varchar { get; set; }
}

[ExpressionCall]
public static class DbFunc {
  static ThreadLocal<ExpressionCallContext> context = new ThreadLocal<ExpressionCallContext>();

  public static string SetDbParameter(this string that, int size)
  {
    if (context.Value.DbParameter != null)
      context.Value.DbParameter.Size = size;
    return context.Value.ParsedContent["that"];
  }
}

第一条语句产生的参数对象 Size 为 100,第二条为 10:

image
image

表达式函数全览

表达式MySqlSqlServerPostgreSQLOracle功能说明
a ? b : ccase when a then b else c endcase when a then b else c endcase when a then b else c endcase when a then b else c enda成立时取b值,否则取c值
a ?? bifnull(a, b)isnull(a, b)coalesce(a, b)nvl(a, b)当a为null时,取b值
数字 + 数字a + ba + ba + ba + b数字相加
数字 + 字符串concat(a, b)cast(a as varchar) + cast(b as varchar)case(a as varchar)|| ba|| b字符串相加,a或b任意一个为字符串时
a - ba - ba - ba - ba - b
a * ba * ba * ba * ba * b
a / ba / ba / ba / ba / b
a / ba div ba / ba / btrunc(a / b)整除(a,b都为整数)
a % ba % ba % ba % bmod(a,b)

等等...

数组

表达式MySqlSqlServerPostgreSQLOracle功能说明
a.Length--case when a is null then 0 else array_length(a,1) end-数组长度
常量数组.Length--array_length(array[常量数组元素逗号分割],1)-数组长度
a.Any()--case when a is null then 0 else array_length(a,1) end > 0-数组是否为空
常量数组.Contains(b)b in (常量数组元素逗号分割)b in (常量数组元素逗号分割)b in (常量数组元素逗号分割)b in (常量数组元素逗号分割)IN查询
a.Contains(b)--a @> array[b]-a数组是否包含b元素
a.Concat(b)--a || b-数组相连
a.Count()--同 Length-数组长度

一个细节证明 FreeSql 匠心制作

通用的 in 查询 select.Where(a => new []{ 1,2,3 }.Contains(a.xxx))

假设 xxxs 是 pgsql 的数组字段类型,其实会与上面的 in 查询起冲突,FreeSql 解决了这个矛盾 select.Where(a => a.xxxs.Contains(1))

字典 Dictionary<string, string>

表达式MySqlSqlServerPostgreSQLOracle功能说明
a.Count--case when a is null then 0 else array_length(akeys(a),1) end-字典长度
a.Keys--akeys(a)-返回字典所有key数组
a.Values--avals(a)-返回字典所有value数组
a.Contains(b)--a @> b-字典是否包含b
a.ContainsKey(b)--a? b-字典是否包含key
a.Concat(b)--a || b-字典相连
a.Count()--同 Count-字典长度

JSON JToken/JObject/JArray

表达式MySqlSqlServerPostgreSQLOracle功能说明
a.Count--jsonb_array_length(coalesce(a, '[]))-json数组类型的长度
a.Any()--jsonb_array_length(coalesce(a, '[])) > 0-json数组类型,是否为空
a.Contains(b)--coalesce(a, '{}') @> b::jsonb-json中是否包含b
a.ContainsKey(b)--coalesce(a, '{}') ? b-json中是否包含键b
a.Concat(b)--coalesce(a, '{}')b::jsonb
Parse(a)--a::jsonb-转化字符串为json类型
a.Field["x"]--a.Field->x-json成员访问

字符串

表达式MySqlSqlServerPostgreSQLOracleSqlite
string.Empty''''''''
string.IsNullOrEmpty(a)(a is null or a = '')(a is null or a = '')(a is null or a = '')(a is null or a = '')(a is null or a = '')
string.Concat(a,b,c...)concat(a, b, c)a + b + ca || b || ca || b || ca || b || c
a.CompareTo(b)strcmp(a, b)-case when a = b then 0 when a > b then 1 else -1 endcase when a = b then 0 when a > b then 1 else -1 endcase when a = b then 0 when a > b then 1 else -1 end
a.Contains('b')a like '%b%'a like '%b%'a ilike'%b%'a like '%b%'a like '%b%'
a.EndsWith('b')a like '%b'a like '%b'a ilike'%b'a like '%b'a like '%b'
a.IndexOf(b)locate(a, b) - 1locate(a, b) - 1strpos(a, b) - 1instr(a, b, 1, 1) - 1instr(a, b) - 1
a.Lengthchar_length(a)len(a)char_length(a)length(a)length(a)
a.PadLeft(b, c)lpad(a, b, c)-lpad(a, b, c)lpad(a, b, c)lpad(a, b, c)
a.PadRight(b, c)rpad(a, b, c)-rpad(a, b, c)rpad(a, b, c)rpad(a, b, c)
a.Replace(b, c)replace(a, b, c)replace(a, b, c)replace(a, b, c)replace(a, b, c)replace(a, b, c)
a.StartsWith('b')a like 'b%'a like 'b%'a ilike'b%'a like 'b%'a like 'b%'
a.Substring(b, c)substr(a, b, c + 1)substring(a, b, c + 1)substr(a, b, c + 1)substr(a, b, c + 1)substr(a, b, c + 1)
a.ToLowerlower(a)lower(a)lower(a)lower(a)lower(a)
a.ToUpperupper(a)upper(a)upper(a)upper(a)upper(a)
a.Trimtrim(a)trim(a)trim(a)trim(a)trim(a)
a.TrimEndrtrim(a)rtrim(a)rtrim(a)rtrim(a)rtrim(a)
a.TrimStartltrim(a)ltrim(a)ltrim(a)ltrim(a)ltrim(a)
a.FirstOrDefaultsubstr(a,1,1)substring(a,1,1)substr(a,1,1)substr(a,1,1)substr(a,1,1)

使用字符串函数可能会出现性能瓶颈,虽然不推荐使用,但是作为功能库这也是不可缺少的功能之一。

日期

表达式MySqlSqlServerPostgreSQLOracle
DateTime.Nownow()getdate()current_timestampsystimestamp
DateTime.UtcNowutc_timestamp()getutcdate()(current_timestamp at time zone 'UTC')sys_extract_utc(systimestamp)
DateTime.Todaycurdateconvert(char(10),getdate(),120)current_datetrunc(systimestamp)
DateTime.MaxValuecast('9999/12/31 23:59:59' as datetime)'9999/12/31 23:59:59''9999/12/31 23:59:59'::timestampto_timestamp('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS.FF6')
DateTime.MinValuecast('0001/1/1 0:00:00' as datetime)'1753/1/1 0:00:00''0001/1/1 0:00:00'::timestampto_timestamp('0001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF6')
DateTime.Compare(a, b)a - ba - bextract(epoch from a::timestamp-b::timestamp)extract(day from (a-b))
DateTime.DaysInMonth(a, b)dayofmonth(last_day(concat(a, '-', b, '-1')))datepart(day, dateadd(day, -1, dateadd(month, 1, cast(a as varchar) + '-' + cast(b as varchar) + '-1')))extract(day from (a
DateTime.Equals(a, b)a = ba = ba = ba = b
DateTime.IsLeapYear(a)a%4=0 and a%100<>0 or a%400=0a%4=0 and a%100<>0 or a%400=0a%4=0 and a%100<>0 or a%400=0mod(a,4)=0 AND mod(a,100)<>0 OR mod(a,400)=0
DateTime.Parse(a)cast(a as datetime)cast(a as datetime)a::timestampto_timestamp(a,'YYYY-MM-DD HH24:MI:SS.FF6')
a.Add(b)date_add(a, interval b microsecond)dateadd(millisecond, b / 1000, a)a::timestamp+(b
a.AddDays(b)date_add(a, interval b day)dateadd(day, b, a)a::timestamp+(b
a.AddHours(b)date_add(a, interval b hour)dateadd(hour, b, a)a::timestamp+(b
a.AddMilliseconds(b)date_add(a, interval b*1000 microsecond)dateadd(millisecond, b, a)a::timestamp+(b
a.AddMinutes(b)date_add(a, interval b minute)dateadd(minute, b, a)a::timestamp+(b
a.AddMonths(b)date_add(a, interval b month)dateadd(month, b, a)a::timestamp+(b
a.AddSeconds(b)date_add(a, interval b second)dateadd(second, b, a)a::timestamp+(b
a.AddTicks(b)date_add(a, interval b/10 microsecond)dateadd(millisecond, b / 10000, a)a::timestamp+(b
a.AddYears(b)date_add(a, interval b year)dateadd(year, b, a)a::timestamp+(b
a.Datecast(date_format(a, '%Y-%m-%d') as datetime)convert(char(10),a,120)a::datetrunc(a)
a.Daydayofmonth(a)datepart(day, a)extract(day from a::timestamp)cast(to_char(a,'DD') as number)
a.DayOfWeekdayofweek(a)datepart(weekday, a) - 1extract(dow from a::timestamp)case when to_char(a)='7' then 0 else cast(to_char(a) as number) end
a.DayOfYeardayofyear(a)datepart(dayofyear, a)extract(doy from a::timestamp)cast(to_char(a,'DDD') as number)
a.Hourhour(a)datepart(hour, a)extract(hour from a::timestamp)cast(to_char(a,'HH24') as number)
a.Millisecondfloor(microsecond(a) / 1000)datepart(millisecond, a)extract(milliseconds from a::timestamp)-extract(second from a::timestamp)*1000cast(to_char(a,'FF3') as number)
a.Minuteminute(a)datepart(minute, a)extract(minute from a::timestamp)cast(to_char(a,'MI') as number)
a.Monthmonth(a)datepart(month, a)extract(month from a::timestamp)cast(to_char(a,'FF3') as number)
a.Secondsecond(a)datepart(second, a)extract(second from a::timestamp)cast(to_char(a,'SS') as number)
a.Subtract(b)timestampdiff(microsecond, b, a)datediff(millisecond, b, a) * 1000(extract(epoch from a::timestamp-b::timestamp)*1000000)a - b
a.Tickstimestampdiff(microsecond, '0001-1-1', a) * 10datediff(millisecond, '1970-1-1', a) * 10000 + 621355968000000000extract(epoch from a::timestamp)*10000000+621355968000000000cast(to_char(a,'FF7') as number)
a.TimeOfDaytimestampdiff(microsecond, date_format(a, '%Y-%m-%d'), a)'1970-1-1 ' + convert(varchar, a, 14)extract(epoch from a::time)*1000000a - trunc(a)
a.Yearyear(a)datepart(year, a)extract(year from a::timestamp)
a.Equals(b)a = ba = ba = ba = b
a.CompareTo(b)a - ba - ba - ba - b
a.ToString()date_format(a, '%Y-%m-%d %H:%i:%s.%f')convert(varchar, a, 121)to_char(a, 'YYYY-MM-DD HH24:MI:SS.US')to_char(a,'YYYY-MM-DD HH24:MI:SS.FF6')

时间

表达式MySql(微秒)SqlServer(秒)PostgreSQL(微秒)Oracle(Interval day(9) to second(7))
TimeSpan.Zero00-0微秒
TimeSpan.MaxValue922337203685477580922337203685477580-numtodsinterval(233720368.5477580,'second')
TimeSpan.MinValue-922337203685477580-922337203685477580-numtodsinterval(-233720368.5477580,'second')
TimeSpan.Compare(a, b)a - ba - b-extract(day from (a-b))
TimeSpan.Equals(a, b)a = ba = b-a = b
TimeSpan.FromDays(a)a 1000000 60 60 24a 1000000 60 60 24-numtodsinterval(a*86400,'second')
TimeSpan.FromHours(a)a 1000000 60 * 60a * 1000000 60 60-numtodsinterval(a*3600,'second')
TimeSpan.FromMilliseconds(a)a * 1000a * 1000-numtodsinterval(a/1000,'second')
TimeSpan.FromMinutes(a)a 1000000 60a 1000000 60-numtodsinterval(a*60,'second')
TimeSpan.FromSeconds(a)a * 1000000a * 1000000-numtodsinterval(a,'second')
TimeSpan.FromTicks(a)a / 10a / 10-numtodsinterval(a/10000000,'second')
a.Add(b)a + ba + b-a + b
a.Subtract(b)a - ba - b-a - b
a.CompareTo(b)a - ba - b-extract(day from (a-b))
a.Daysa div (1000000 60 60 * 24)a div (1000000 * 60 60 24)-extract(day from a)
a.Hoursa div (1000000 60 60) mod 24a div (1000000 60 60) mod 24-extract(hour from a)
a.Millisecondsa div 1000 mod 1000a div 1000 mod 1000-cast(substr(extract(second from a)-floor(extract(second from a)),2,3) as number)
a.Secondsa div 1000000 mod 60a div 1000000 mod 60-extract(second from a)
a.Ticksa * 10a * 10-(extract(day from a)*86400+extract(hour from a)*3600+extract(minute from a)*60+extract(second from a))*10000000
a.TotalDaysa / (1000000 60 60 * 24)a / (1000000 * 60 60 24)-extract(day from a)
a.TotalHoursa / (1000000 60 60)a / (1000000 60 60)-(extract(day from a)*24+extract(hour from a))
a.TotalMillisecondsa / 1000a / 1000-(extract(day from a)*86400+extract(hour from a)*3600+extract(minute from a)*60+extract(second from a))*1000
a.TotalMinutesa / (1000000 * 60)a / (1000000 * 60)-
a.TotalSecondsa / 1000000a / 1000000-(extract(day from a)*86400+extract(hour from a)*3600+extract(minute from a)*60+extract(second from a))
a.Equals(b)a = ba = b-a = b
a.ToString()cast(a as varchar)cast(a as varchar)-to_char(a)

数学函数

表达式MySqlSqlServerPostgreSQLOracle
Math.Abs(a)abs(a)abs(a)abs(a)
Math.Acos(a)acos(a)acos(a)acos(a)acos(a)
Math.Asin(a)asin(a)asin(a)asin(a)asin(a)
Math.Atan(a)atan(a)atan(a)atan(a)atan(a)
Math.Atan2(a, b)atan2(a, b)atan2(a, b)atan2(a, b)-
Math.Ceiling(a)ceiling(a)ceiling(a)ceiling(a)ceil(a)
Math.Cos(a)cos(a)cos(a)cos(a)cos(a)
Math.Exp(a)exp(a)exp(a)exp(a)exp(a)
Math.Floor(a)floor(a)floor(a)floor(a)floor(a)
Math.Log(a)log(a)log(a)log(a)log(e,a)
Math.Log10(a)log10(a)log10(a)log10(a)log(10,a)
Math.PI(a)3.14159265358979313.14159265358979313.14159265358979313.1415926535897931
Math.Pow(a, b)pow(a, b)power(a, b)pow(a, b)power(a, b)
Math.Round(a, b)round(a, b)round(a, b)round(a, b)round(a, b)
Math.Sign(a)sign(a)sign(a)sign(a)sign(a)
Math.Sin(a)sin(a)sin(a)sin(a)sin(a)
Math.Sqrt(a)sqrt(a)sqrt(a)sqrt(a)sqrt(a)
Math.Tan(a)tan(a)tan(a)tan(a)tan(a)
Math.Truncate(a)truncate(a, 0)floor(a)trunc(a, 0)trunc(a, 0)

类型转换

表达式MySqlSqlServerPostgreSQLOracleSqlite
Convert.ToBoolean(a) | bool.Parse(a)a not in ('0','false')a not in ('0','false')a::varchar not in ('0','false','f','no')-a not in ('0','false')
Convert.ToByte(a) | byte.Parse(a)cast(a as unsigned)cast(a as tinyint)a::int2cast(a as number)cast(a as int2)
Convert.ToChar(a)substr(cast(a as char),1,1)substring(cast(a as nvarchar),1,1)substr(a::char,1,1)substr(to_char(a),1,1)substr(cast(a as character),1,1)
Convert.ToDateTime(a) | DateTime.Parse(a)cast(a as datetime)cast(a as datetime)a::timestampto_timestamp(a,'YYYY-MM-DD HH24:MI:SS.FF6')datetime(a)
Convert.ToDecimal(a) | decimal.Parse(a)cast(a as decimal(36,18))cast(a as decimal(36,19))a::numericcast(a as number)cast(a as decimal(36,18))
Convert.ToDouble(a) | double.Parse(a)cast(a as decimal(32,16))cast(a as decimal(32,16))a::float8cast(a as number)cast(a as double)
Convert.ToInt16(a) | short.Parse(a)cast(a as signed)cast(a as smallint)a::int2cast(a as number)cast(a as smallint)
Convert.ToInt32(a) | int.Parse(a)cast(a as signed)cast(a as int)a::int4cast(a as number)cast(a as smallint)
Convert.ToInt64(a) | long.Parse(a)cast(a as signed)cast(a as bigint)a::int8cast(a as number)cast(a as smallint)
Convert.ToSByte(a) | sbyte.Parse(a)cast(a as signed)cast(a as tinyint)a::int2cast(a as number)cast(a as smallint)
Convert.ToSingle(a) | float.Parse(a)cast(a as decimal(14,7))cast(a as decimal(14,7))a::float4cast(a as number)cast(a as float)
Convert.ToString(a)cast(a as char)cast(a as nvarchar)a::varcharto_char(a)cast(a as character)
Convert.ToUInt16(a) | ushort.Parse(a)cast(a as unsigned)cast(a as smallint)a::int2cast(a as number)cast(a as unsigned)
Convert.ToUInt32(a) | uint.Parse(a)cast(a as unsigned)cast(a as int)a::int4cast(a as number)cast(a as decimal(10,0))
Convert.ToUInt64(a) | ulong.Parse(a)cast(a as unsigned)cast(a as bigint)a::int8cast(a as number)cast(a as decimal(21,0))
Guid.Parse(a)substr(cast(a as char),1,36)cast(a as uniqueidentifier)a::uuidsubstr(to_char(a),1,36)substr(cast(a as character),1,36)
Guid.NewGuid()-newid()---
new Random().NextDouble()rand()rand()random()dbms_random.valuerandom()