SELECT 语句
更新时间：2026-07-24
查询的唯一入口语句。整体形态：
Plain Text
1[WITH cte_name AS (subquery)[, ...]]
2SELECT [DISTINCT] select_expression[, ...]
3FROM table_or_subquery[, ...]
4[WHERE condition]
5[GROUP BY expression[, ...]]
6[HAVING condition]
7[UNION [ALL] select_statement]
8[ORDER BY expression [ASC | DESC][, ...]]
9[LIMIT count]
10[OFFSET count]本页示例基于 temps 表（tag 列 room，field 列 temp，每分钟一个点）与 rooms 表（tag 列 room，field 列 floor）。
SELECT 列表与 DISTINCT
选择列、表达式、函数调用，可用 AS 起别名；SELECT DISTINCT 对结果行去重：
SQL
1> SELECT DISTINCT room FROM temps ORDER BY room;
2+---------+
3| room |
4+---------+
5| kitchen |
6| living |
7+---------+WHERE
按条件过滤行。时序查询几乎总是带 time 范围过滤，这也是分区裁剪的依据：
SQL
1SELECT time, room, temp
2FROM temps
3WHERE time >= now() - INTERVAL '1 hour' AND room = 'kitchen';GROUP BY 与 HAVING
GROUP BY 按表达式分组聚合；HAVING 在聚合之后过滤分组：
SQL
1> SELECT room, avg(temp) AS avg_temp FROM temps GROUP BY room HAVING avg(temp) > 22 ORDER BY room;
2+---------+--------------------+
3| room | avg_temp |
4+---------+--------------------+
5| kitchen | 22.333333333333332 |
6+---------+--------------------+时序降采样的标准形态是按 date_bin（或补缺口的 date_bin_gapfill）分组，见 时间日期函数。
ORDER BY、LIMIT 与 OFFSET
排序与截断。ORDER BY 可多列、可 ASC / DESC；LIMIT 限制行数，OFFSET 跳过行：
SQL
1> SELECT time, room, temp FROM temps ORDER BY time DESC LIMIT 2;
2+---------------------+---------+------+
3| time | room | temp |
4+---------------------+---------+------+
5| 2024-01-01T00:02:00 | living | 23.0 |
6| 2024-01-01T00:02:00 | kitchen | 24.5 |
7+---------------------+---------+------+
SQL
1> SELECT time, room, temp FROM temps ORDER BY time, room LIMIT 2 OFFSET 2;
2+---------------------+---------+------+
3| time | room | temp |
4+---------------------+---------+------+
5| 2024-01-01T00:01:00 | kitchen | 22.5 |
6| 2024-01-01T00:01:00 | living | 21.5 |
7+---------------------+---------+------+WITH（公共表表达式）
WITH 定义命名子查询，供主查询引用，适合把多步过滤/聚合写清楚：
SQL
1> WITH hot AS (SELECT * FROM temps WHERE temp > 22) SELECT room, count(*) AS n FROM hot GROUP BY room ORDER BY room;
2+---------+---+
3| room | n |
4+---------+---+
5| kitchen | 2 |
6| living | 1 |
7+---------+---+UNION
合并两个查询的结果集：UNION 去重，UNION ALL 保留重复行。两侧列数与类型必须兼容：
SQL
1> SELECT room FROM temps WHERE temp > 24 UNION SELECT room FROM rooms WHERE floor = 2 ORDER BY room;
2+---------+
3| room |
4+---------+
5| kitchen |
6| living |
7+---------+子查询
FROM 中可使用子查询（派生表），WHERE 中可使用标量子查询与 IN (subquery)：
SQL
1SELECT room, avg_temp
2FROM (SELECT room, avg(temp) AS avg_temp FROM temps GROUP BY room)
3WHERE avg_temp > 22;JOIN
跨表 JOIN 与自 JOIN 均可用。等值 join key 支持 tag、time 以及类型转换后的表达式；跨表查询应使用表别名限定同名列：
SQL
1SELECT t.time, t.room, t.temp, r.floor
2FROM temps AS t
3JOIN rooms AS r ON t.room = r.room
4ORDER BY t.time;自 JOIN 示例：
SQL
1> SELECT a.time, a.room, a.temp FROM temps a JOIN temps b ON a.time = b.time AND a.room = b.room ORDER BY a.time, a.room LIMIT 3;
2+---------------------+---------+------+
3| time | room | temp |
4+---------------------+---------+------+
5| 2024-01-01T00:00:00 | kitchen | 20.0 |
6| 2024-01-01T00:00:00 | living | 21.0 |
7| 2024-01-01T00:01:00 | kitchen | 22.5 |
8+---------------------+---------+------+评价此篇文章