ArcEngine接口IQueryDef实现常见SQL

文件地理数据库允许在 QueryDef 中通过 SubFields(字段列表)方法使用表达式和别名完善分析和报告。此外,通过 PostFixClause 支持 ORDER BY 和 GROUP BY。在支持 GROUP BY 时,表达式包括聚合函数,如 MIN、MAX 和 SUM。
ALIAS
提供给列另外一个名称以提供更易于理解的输出。

SQL

SELECT NAME, POP1997 - POP1990 as PopChange
FROM counties ORDER BY NAME


ArcObjects

pQueryDef.SubFields = "NAME, POP1997 - POP1990 as PopChange"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY NAME"


CASE
评估条件列表并返回多个结果表达式之一。

SQL

SELECT name,salary,
CASE
    WHEN salary <= 2000 THEN 'low'
    WHEN salary > 2000 AND salary <= 3000 THEN 'average'
    WHEN salary > 3000 THEN 'high'
    END AS salary_level
    FROM employees
    ORDER BY salary ASC


ArcObjects

pQueryDef.SubFields = "name,salary,
CASE
    WHEN salary <= 2000 THEN 'low'
    WHEN salary > 2000 AND salary <= 3000 THEN 'average'
    WHEN salary > 3000 THEN 'high'
    END AS salary_level"
    pQueryDef.Tables = " employees"
    pQueryDef.PostfixClause = " ORDER BY salary ASC "


COALESCE
返回其参数的第一个非空字段值。

SQL

SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone)
Contact_Phone
FROM Contact_Info


ArcObjects

queryDef.SubFields = "Name, COALESCE(Business_Phone, Cell_Phone,
Home_Phone) Contact_Phone"
queryDef.Tables = "Contact_Info"


Contact_Info:

表达式
SQL

SELECT SIN(sunangle)
FROM sightings


ArcObjects

queryDef.SubFields = "SIN(sunangle)"
queryDef.Tables = "sightings"


设置函数

算述运算符
算术运算符用于对数值进行加、减、乘、除的运算。

 

函数
以下是文件地理数据库支持的整个函数列表。

字符串函数
以 string_exp 表示的参数可以是列名、字符串文本或者另一个标量函数的结果,其基础数据类型可表示为字符型。

以 character_exp 表示的参数是长度可变的字符型字符串。

以 start 或 length 表示的参数可以是数值文本或者另一个标量函数的结果,其基础数据类型可表示为数值型。

这些字符串函数以 1 为基础;即字符串的第一个字符为字符 1。

数值函数
所有数值函数均返回数值型值。

以 numeric_exp、float_exp 或 integer_exp 表示的参数可以是列名、另一个标量函数的结果或数值文本,其基础数据类型可表示为数值型。

函数   描述
ABS(numeric_exp)返回 numeric_exp 的绝对值。
ACOS(float_exp)返回作为角度的 float_exp 的反余弦值,用弧度表示。
ASIN(float_exp)返回作为角度的 float_exp 的反正弦值,用弧度表示。
ATAN(float_exp)返回作为角度的 float_exp 的反正切值,用弧度表示。
CEILING(numeric_exp)返回大于或等于 numeric_exp 的最小整数。
COS(float_exp)返回 float_exp 的余弦值,其中 float_exp是以弧度表示的角度。
FLOOR(numeric_exp)返回小于或等于 numeric_exp 的最大整数。
LOG(float_exp)返回 float_exp 的自然对数。
LOG10(float_exp)返回 float_exp 的以 10 为底的对数。
MOD(integer_exp1, integer_exp2)返回 integer_exp1 除以 integer_exp2 所得的余数。
POWER(numeric_exp, integer_exp)返回 numeric_exp 的 integer_exp 次幂的值。
ROUND(numeric_exp, integer_exp)返回四舍五入至小数点右侧第 integer_exp位的 numeric_exp。如果 integer_exp 为负数,则 numeric_exp 将被四舍五入至小数点左侧第 |integer_exp| 位。
SIGN(numeric_exp)返回 numeric_exp 正负号的标志。如果numeric_exp 小于零,则返回 -1。如果numeric_exp 等于零,则返回 0。如果numeric_exp 大于零,则返回 1。
SIN(float_exp)返回 float_exp 的正弦值,其中 float_exp是以弧度表示的角度。
TAN(float_exp)返回 float_exp 的正切值,其中 float_exp是以弧度表示的角度。
TRUNCATE(numeric_exp, integer_exp)返回截断至小数点右侧第 integer_exp 位的numeric_exp。如果 integer_exp 为负数,则 numeric_exp 将被截断至小数点左侧第 |integer_exp| 位。

GROUP BY
用于按一或多列收集多条记录的数据并对结果进行分组

SQL

SELECT STATE_NAME, SUM(POP1990) as TotalPopulation FROM counties
GROUP BY STATE_NAME ORDER BY STATE_NAME


ArcObjects

pQueryDef.SubFields = "STATE_NAME, SUM(POP1990) as TotalPopulation"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "GROUP BY STATE_NAME ORDER BY STATE_NAME"


HAVING
GROUP BY 的子句。

SQL

SELECT department, MAX(salary) as Highest_salary
FROM employees
GROUP BY department HAVING MAX(salary) < 50000


ArcObjects

queryDef.SubFields = "department, MAX(salary) as Highest_salary"
queryDef.Tables = "employees"
pQueryDef.PostfixClause = "GROUP BY department HAVING MAX(salary)< 50000"


JOINS
合并两个或多个表的记录。

Cross Join
SQL

SELECT Table1.name, Table1.Address, Table2.name, Table2.Salary
FROM Table1 CROSS JOIN Table2


ArcObjects

queryDef.SubFields = "Table1.name, Table1.Address, Table2.name,Table2.Salary"
queryDef.Tables = "Table1 CROSS JOIN Table2"


Inner Join
SQL

SELECT Table1.C1, Table1.C2, Table2.C3, Table2.C4 FROM Table1
INNER JOIN Table2 ON Table1.C1 = Table2.C3


ArcObjects

queryDef.SubFields = "Table1.C1, Table1.C2, Table2.C3,Table2.C4"
queryDef.Tables = "Table1 INNER JOIN Table2 ON Table1.C1 =Table2.C3"


Left Outer Join
SQL

SELECT Table1.C1, Table1.C2, Table2.C3, Table2.C4 FROM Table1
LEFT OUTER JOIN Table2 ON Table1.C1 = Table2.C3


ArcObjects

queryDef.SubFields = "Table1.C1, Table1.C2, Table2.C3,Table2.C4"
queryDef.Tables = "Table1 LEFT OUTER JOIN Table2 ON Table1.C1 =Table2.C3"


Right Outer Join
SQL

SELECT * FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.C1 = Table2.C3


ArcObjects

queryDef.SubFields = "*"
queryDef.Tables = "Table1 RIGHT OUTER JOIN Table2 ON Table1.C1 = Table2.C3"


NULLIF
如果所提供的两个参数相等,则 NULLIF 返回空值;否则,返回第一个参数的值。

SQL

SELECT Location, NULLIF(Sales, Forecast) as Results FROM StoreSales


ArcObjects

queryDef.SubFields = " Location, NULLIF(Sales, Forecast) as Results"
queryDef.Tables = "StoreSales"


StoreSales:

结果:

ORDER BY
指定排序顺序。顺序可以是升序 (ASC) 或降序 (DESC),并将两者加以整理。排序规则类型包括 BINARY(BIN)、CASESENSITIVE(CASE) 和 NOCASESENSITIVE(NOCASE)。二进制排序规则区分大小写和重音。区分大小写排序规则区分大写和小写字母。不区分大小写排序规则不区分大写和小写字母。

SQL

SELECT STATE_NAME, POP1990
FROM counties ORDER BY STATE_NAME


ArcObjects

pQueryDef.SubFields = "STATE_NAME, POP1990"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY STATE_NAME"


SQL

SELECT STATE_NAME, POP1990
FROM counties ORDER BY STATE_NAME COLLATE BINARY ASC


ArcObjects

pQueryDef.SubFields = "STATE_NAME, POP1990"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY STATE_NAME COLLATE BINARY ASC"


--------------------- 
原文:https://blog.csdn.net/kone0611/article/details/72865416 
 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值