关于Tdengine按列排序

Question

最近，我使用TDengine在TDengine上通过连接RESTful JDBC连接器来执行RESTful慢速查询记录。我需要根据“成本时间”字段对结果进行排序，但是返回的结果显示错误，声称只支持按标记或时间戳排序(主列)。我查看了TDengine的正式文档，但没有找到更多的信息。TDengine是否无法支持按非主键字段进行排序的功能，或者是否有一种与mysql-orderby不同的排序方法还不知道？

描述表格：

describe test.testtable

{. "status":"succ",
   "head":["Field","Type","Length","Note"],
   "column_meta":[["Field",8,64],["Type",8,20],["Length",4,4], ["Note",8,8]],
   "data":[["ts","TIMESTAMP",8,""],["col1","NCHAR",8,""]],"rows":2}

选择表

select * from test.testtable order by col1

{
   "status" : "error",
   "code" : 512,
   "desc" : "invalid SQL: invalid operation: order by primary 
      timestamp, first tag or groupby column in groupby clause allowed"
}

Answer 1

在TDengine数据库中，只允许在group子句中使用主时间戳和标记作为order列，您可以使用taos查找它。

taos> desc stb;
             Field              |         Type         |   Length    |   Note   |
=================================================================================
 ts                             | TIMESTAMP            |           8 |          |
 c0                             | INT                  |           4 |          |
 c1                             | NCHAR                |          70 |          |
 c2                             | INT                  |           4 |          |
 c3                             | INT                  |           4 |          |
 c4                             | FLOAT                |           4 |          |
 c5                             | INT                  |           4 |          |
 c6                             | INT                  |           4 |          |
 t0                             | NCHAR                |          20 | TAG      |
 t1                             | INT                  |           4 | TAG      |
Query OK, 10 row(s) in set (0.000174s)

taos> select * from stb order by c0 desc limit 10;

DB error: invalid operation: only primary timestamp, first tag/tbname in groupby clause allowed as order column (0.000916s)
taos> select * from stb order by t1 desc limit 10;

DB error: invalid operation: only tag in groupby clause allowed in order clause (0.000825s)

taos> select * from stb order by ts desc limit 3;
           ts            |     c0      |               c1               |     c2      |     c3      |          c4          |     c5      |     c6      |               t0               |     t1      |
=======================================================================================================================================================================================================
 2020-10-01 00:00:00.099 |  -219661821 | G8cMQWdJm8J9w1hFRYIQP8dk4xU... |    78772435 |  -410543182 |        -950962.43750 |  -557459241 |   116557188 | shanghai                       |          74 |
 2020-10-01 00:00:00.099 |  -457689130 | 8UHa3VDA7NZmsGh7EGClOYvlip6... |    82077056 |   446259055 |        2141141.00000 |   848015664 |   575700881 | beijing                        |          73 |
 2020-10-01 00:00:00.099 |    66595749 | EGafmHXYKdcHzzdRasxCKHJiEaX... |  -203469760 |    17904570 |        2102826.50000 |  -997489652 |  -148873667 | shanghai                       |          72 |
Query OK, 3 row(s) in set (0.059262s)

taos> select count(*) from stb group by t1 order by t1 desc slimit 5;
       count(*)        |     t1      |
======================================
                   100 |          99 |
                   100 |          98 |
                   100 |          97 |
                   100 |          96 |
                   100 |          95 |
Query OK, 5 row(s) in set (0.002158s)