1. ECSQL

ECSQL 是针对 iModel 或 ECDb 文件中的业务数据的 CRUD（创建、读取、更新、删除）操作的基于文本的命令语言。

ECSQL 是 SQL 一种实现-一种经过验证的、经过充分采用的基于文本的命令语言。尽可能遵守标准 SQL （SQL-92 和 SQL-99）。

ECSQL 和 SQL 之间的主要区别在于，ECSQL 针对的是逻辑模式，而不是基础数据库的持久性模式。

1.1. ECSQL 参数

• ? ： 占位参数，按顺序赋值；

  SELECT ECInstanceId FROM bis.GeometricElement3d WHERE Model=? AND LastMod>=?
  

• :aaa ： 命名参数。这允许将相同的值绑定到多个占位符。

  SELECT ECInstanceId FROM bis.GeometricElement3d LIMIT :pagesize OFFSET (:pageno * :pagesize)
  

// 查询构件数目，真实世界存在的构件
SELECT count(*) FROM bis.spatialElement ,

// 查询每个实际元素类型在 iModel 中有多少空间元素
SELECT ECClassId, count(*) ElementCount FROM bis.SpatialElement GROUP BY ECClassId ORDER BY ECClassId

// 支持LIMIT 和 OFFSET 分页参数查询，即查询11-15行的数据
SELECT ECInstanceId, ECClassId, CodeValue FROM bis.SpatialLocationElement LIMIT 5 OFFSET 10

// 格式化输出
SELECT count(*) ElementCount FROM bis.SpatialElement

// 基本的数学计算
SELECT 10 Radius, (2 * 3.1415 * 10) Perimeter, (3.1415 * 10 * 10) Area FROM bis.Element LIMIT 1

// NULL 值判定，使用 IS NULL判断，而非 value = NULL（返回空值）
SELECT ECInstanceId,ECClassId FROM bis.SpatialElement WHERE UserLabel IS NULL LIMIT 5

// 时间段查询，支持TIMESTAMP
SELECT ECInstanceId, CodeValue, LastMod FROM bis.Element WHERE LastMod BETWEEN TIMESTAMP '2020-03-11T12:30:20.492Z' AND TIMESTAMP '2020-03-11T12:31:03.494Z'

// IIF 关键字
-- Returns 'Big' if Length is greater than 1, and 'Small' otherwise
SELECT IIF(Length > 1.0, 'Big', 'Small') FROM test.Foo;

-- Returns DisplayLabel if Name is NULL, and Name otherwise
SELECT IF(Name IS NULL, DisplayLabel, Name) FROM test.Foo;

// 复杂的CASE-WHEN-THEN-ELSE
CASE
      WHEN <expr> THEN <expr>
    [ WHEN <expr> THEN <expr> ...]
    [ ELSE <expr> ]
END

// Demo
-- CASE without ELSE. Returns NULL if the IF case is not met
SELECT
    CASE
         WHEN Length > 1 THEN 'Big'
    END
FROM test.Foo

-- CASE with ELSE. If Length is not greater than 1 then the ELSE expression is returned.
SELECT
    CASE
        WHEN Length > 1 THEN 'Big'
        ELSE 'Small'
    END
FROM test.Foo

-- Multiple CASE with ELSE
SELECT
    CASE
        WHEN weekDay=1 THEN 'Mon'
        WHEN weekDay=2 THEN 'Tue'
        WHEN weekDay=3 THEN 'Wen'
        WHEN weekDay=4 THEN 'Thr'
        WHEN weekDay=5 THEN 'Fri'
        WHEN weekDay=6 THEN 'Sat'
        WHEN weekDay=7 THEN 'Sun'
        ELSE 'Wrong value'
    END
FROM test.Foo

// 几何查询，例如查询构件的包围盒
// 详情可查询： https://www.itwinjs.org/learning/geometrysqlfuncs/
  SELECT
    iModel_bbox_union(
      iModel_placement_aabb(
        iModel_placement(
          iModel_point(g.Origin.X, g.Origin.Y, g.Origin.Z),
          iModel_angles(g.Yaw, g.Pitch, g.Roll),
          iModel_bbox(g.BBoxLow.X, g.BBoxLow.Y, g.BBoxLow.Z, g.BBoxHigh.X, g.BBoxHigh.Y, g.BBoxHigh.Z)
        )
      )
    )
  FROM ${Element.classFullName} AS e, ${GeometricElement3d.classFullName} AS g
    WHERE e.model.id=? AND e.ecinstanceid=g.ecinstanceid

// 空间关系--Overlap 
// code
const spaceElement: SpatialElement = iModelDb.elements.getElement("0x1000000001f") as SpatialElement;
iModelDb.withPreparedStatement("SELECT e.ECInstanceId, e.ECClassId, e.CodeValue FROM bis.SpatialElement e JOIN bis.SpatialIndex i ON e.ECInstanceId=i.ECInstanceId WHERE i.ECInstanceId MATCH iModel_spatial_overlap_aabb(?) AND e.Category.Id=0x1000000000a",
   (stmt: ECSqlStatement) => {
     stmt.bindRange3d(1, spaceElement.placement.calculateRange());
     while (stmt.step() === DbResult.BE_SQLITE_ROW) {
        const row: any = stmt.getRow();
        console.log(row);
     }
   });

// result
{ id : "0x1000000001e", className: "MyDomain.Story", codeValue: "A-G" }
{ id : "0x10000000023", className: "MyDomain.Story", codeValue: "A-1" }