可以通过描述 query 动态生成一张二维数据表或数值，在该数据表上查询或应用用于 WHERE, HAVING 查询条件。用于 SELECT 子查询场景时，必须设定别名，如设置 query, 则 from 失效。 query 数据结构为 QueryDSL。

Query DSL:

function select_child() {
  var query = new Query();
  var data = query.Get({
    debug: true,
    select: ["id", "name"],
    query: {
      comment: "通过子查询生成一张新数据表(必须设置别名)",
      select: ["id", "name as name"],
      from: "service as m",
      limit: 50,
    },
    orders: "id",
    limit: 20,
  });
  return data;
}

解析后的 SQL语句 :

SELECT `id`, `name`
FROM (SELECT `id`, `name` AS `name` FROM `service` AS `m`) AS `_SUB_`
ORDER BY `id` ASC

Query DSL:

function select_wheres() {
  var query = new Query();
  var data = query.Get({
    select: ["id", "name"],
    from: "service",
    wheres: [
      {
        field: "type",
        op: "in",
        query: {
          comment: "查询查询厂商ID数组",
          select: ["id"],
          from: "service",
        },
      },
    ],
    orders: "id",
    limit: 20,
  });
  return data;
}

解析后的 SQL语句 :

SELECT `id`, `name`
FROM `service`
WHERE `type` IN (SELECT `id` FROM `service`)
ORDER BY `id` ASC

连接两张数据量较大的数据表，非常消耗服务器资源，且查询低效。对于数据统计分析等场景，应该使用数仓或数据分析引擎，根据业务场景设置业务数据结构、清洗归集逻辑和查询逻辑，最终归集为一张宽表，在宽表中进行统计查询。数据量较大的应用，推荐使用象传智慧 Xu 数据分析引擎。

Gou Query DSL:

function joins() {
  var query = new Query();
  var data = query.Get({
    debug: true,
    select: ["service.id", "service.name", "parent.city as city"],
    from: "service",
    joins: [
      {
        left: true,
        from: "service as parent",
        key: "parent.id",
        foreign: "service.id",
      },
    ],
  });
  return data;
}

解析后的 SQL语句 :

select `service`.`id`, `service`.`name`, `parent`.`city` as `city`
from `service`
left join `service` as `parent` on `parent`.`id` = `service`.`id`

查询结果为:

非必要，勿使用 除非无法使用 Query DSL 描述查询逻辑，不要直接编写 SQL 语句。直接编写 SQL 语句将加大应用程序维护难度；增加系统信息泄露安全风险；因查询逻辑不可控，可能造成数据库或数仓宕机；数据迁移、数据库、数仓、数据分析引擎升级，也可导致查询语句不可用。

Gou Query DSL:

{
  "sql": {
    "stmt": "SELECT `id`, `name` FROM `service` WHERE id = ?",
    "args": [1]
  }
}

解析后的 SQL语句 :

SELECT `id`, `name` FROM `service` WHERE id = 1

Object SQL 数据结构