8.7.3.1 MySQL #

语法说明

SELECT [DISTINCT] expr_list
    [FROM [db.]table | (subquery)]
    INNER|LEFT|RIGHT JOIN (subquery)|table ON (expr) | USING columns_list
    [WHERE expr]
    [GROUP BY expr_list]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT [count, ]offset]
    [UNION ALL ...]

举例说明
聚合查询

SELECT `column1`, `column2`, COUNT(`column3`) AS `CNT`, SUM(`column4`) AS `CSUM`
FROM (select 1 as column1, 2 as column2, 'value' as column3, 100 as column4 union all select 2 as column1, 3 as column2, 'value' as column3, 50 as column4 ) /* 这个UNION 语句用于构造临时数据用于查询验证*/
WHERE `column4` > 10
GROUP BY `column1`, `column2`
HAVING `CNT` > 0
ORDER BY `column1`
LIMIT 10

JOIN 联表查询
JOIN 支持 LEFT JOIN \ RIGHT JOIN \ INNER JOIN

SELECT `t1`.`column1`, `t1`.`column2`, COUNT(1) AS `CNT`, SUM(`column4`) AS `CSUM`
FROM (
        SELECT `column1`, `column2`, `column3`
        FROM (select 1 as column1, 2 as column2, 'value' as column3 union all select 2 as column1, 3 as column2, 'value' as column3 ) /* 这个UNION 语句用于构造临时数据用于查询验证*/
        WHERE `column3` = 'value'
) AS `t1` LEFT JOIN (
        SELECT `column1`, `column2`, `column4`
        FROM (select 1 as column1, 2 as column2, 9 as column4 union all select 2 as column1, 3 as column2, 100 as column4 ) /* 这个UNION 语句用于构造临时数据用于查询验证*/
        WHERE `column4` > 10
) AS `t2` ON (`t1`.`column1` = `t2`.`column1` AND `t1`.`column2` = `t2`.`column2`)
GROUP BY `t1`.`column1`, `t1`.`column2`
HAVING `CNT` < 100
ORDER BY `column1`
LIMIT 10

UNION 查询

SELECT `column1`, `column2`, COUNT(`column3`) AS `CNT`, SUM(`column4`) AS `CSUM`
FROM (select 1 as column1, 2 as column2, 'value' as column3, 100 as column4 union all select 2 as column1, 3 as column2, 'value' as column3, 10 as column4 ) /* 这个UNION 语句用于构造临时数据用于查询验证*/
WHERE `column4` > 10
GROUP BY `column1`, `column2`
UNION ALL
SELECT `column1`, `column2`, COUNT(`column3`) AS `CNT`, SUM(`column4`) AS `CSUM`
FROM (select 1 as column1, 2 as column2, 'value' as column3, 100 as column4 union all select 2 as column1, 3 as column2, 'value' as column3, 50 as column4 ) /* 这个UNION 语句用于构造临时数据用于查询验证*/
WHERE `column4` > 10
GROUP BY `column1`, `column2`

DISTINCT 值查询

SELECT DISTINCT `column1`, `column2`
FROM (select 1 as column1, 2 as column2, 'value' as column3, 100 as column4 union all select 1 as column1, 2 as column2, 'value' as column3, 50 as column4 ) /* 这个UNION 语句用于构造临时数据用于查询验证*/

8.7.3.2 ElasticSearch #

SELECT expr_list
    [FROM table | (subquery)]
    [WHERE expr]
    [GROUP BY expr_list]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT count]

ElasticSearch支持的SQL语法限制如下：

1. 不支持 SELECT 中的 DISTINCT 关键字
2. 不支持 JOIN/UNION
3. SUBQUERY 仅支持能够在语义上展开为一层SELECT语句的子查询

8.7.3.3 Hana #

SELECT [DISTINCT] expr_list
    [FROM [db.]table | (subquery)]
    INNER|LEFT|RIGHT JOIN (subquery)|table ON (expr)
    [WHERE expr]
    [GROUP BY expr_list]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT [count, ]offset]
    [UNION ALL ...]

8.7.3.4 HBase #

SELECT expr_list
    [FROM [db.]table]
    [WHERE expr]
    [LIMIT count]

HBase支持的SQL语法限制如下：

1. 仅支持简单SELECT语句，不支持SUBQUERY/JOIN/UNION/AGGREGATION
2. SELECT字段仅支持表中的列，不支持表达式
3. 列名必须加上反引号"`"标注
4. 支持的运算符：
   a)   比较运算符：=/!=/>/>=/</<=
   b)   逻辑运算符：AND/OR
5. 支持的函数：
   a)   match
   b)   match_prefix
   c)   match_substring