时间戳

首页 > 解决方案 > 在 TDengine 中联合所有

td-engine - 在 TDengine 中联合所有

问题描述

最近在TDengine中尝试union all，发现有问题：

taos> select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc;
         count         |              loc               |              loc               |
==========================================================================================
                    10 | nchar0                         | nchar0                         |
                    10 | nchar1                         | nchar1                         |
                    10 | nchar2                         | nchar2                         |
                    10 | nchar3                         | nchar3                         |
                    10 | nchar4                         | nchar4                         |
                    10 | nchar5                         | nchar5                         |
Query OK, 6 row(s) in set (0.003831s)

taos> select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc;
Query OK, 0 row(s) in set (0.002620s)

taos> select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc
   -> union all
   -> select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc;
         count         |              loc               |              loc               |
==========================================================================================
                    10 | nchar0                         | nchar0                         |
                    10 | nchar1                         | nchar1                         |
                    10 | nchar2                         | nchar2                         |
                    10 | nchar3                         | nchar3                         |
                    10 | nchar4                         | nchar4                         |
                    10 | nchar5                         | nchar5                         |
Query OK, 6 row(s) in set (0.004686s)

taos> select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc
   -> union all
   -> select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc;
         count         |              loc               |              loc               |
==========================================================================================

Query OK, 0 row(s) in set (0.004371s)

从以上查询中，为什么查询 3 和查询 4 ​​的结果不同？这让我很困惑。

标签： td-engine

解决方案

因为TDengine的SQL是类SQL语言，我猜它的SQL实现和SQL基本一样。在mySQL中， UNIONALL的实现原理是根据前面的表结构而不是下面的表来生成结果表。例如表1的结构是：

CREATE TABLE `table_1` (
 `col1` int(255) DEFAULT NULL,
 `col2` int(255) DEFAULT NULL,
 `col3` int(255) DEFAULT NULL,
 `col4` int(255) DEFAULT NULL
) ENGINE = InnoDB CHARSET = latin1;

表2的结构是：

CREATE TABLE `table_1` (
 `col4` int(255) DEFAULT NULL,
 `col3` int(255) DEFAULT NULL,
 `col2` int(255) DEFAULT NULL,
 `col1` int(255) DEFAULT NULL
) ENGINE = InnoDB CHARSET = latin1;

表 1 Unionall和表 2 的结果与表 1 的结构相同，表 2 Unionall的结果与表 2的结构相同。

所以对于这个问题，在第二种情况下，前面的表是空的，TDengine可能无法识别它的表结构，所以结果表结构也是空的，导致结果不一致。

推荐阅读