SQL 速查表
2026-10-16 15:48:05示例PostgreSQL 与 SQLite:整数 / 整数向零截断,运算顺序会改变百分比。
sql复制-- PostgreSQL and SQLite: integer / integer truncates toward zero, so a ratio of counts becomes 0.
CREATE TABLE stats (id INT, hits INT, total INT);
INSERT INTO stats VALUES (1, 1, 3), (2, 7, 2), (3, 5, 0);
SELECT id, hits / NULLIF(total, 0) AS ratio FROM stats ORDER BY id;
-- => 1 | 0
-- => 2 | 3
-- => 3 | NULL
SELECT id, hits / NULLIF(total, 0) * 100 AS pct_wrong, 100 * hits / NULLIF(total, 0) AS pct_truncated FROM stats ORDER BY id;
-- => 1 | 0 | 33
-- => 2 | 300 | 350
-- => 3 | NULL | NULL
SELECT -7 / 2 AS quotient, -7 % 2 AS remainder;
-- => -3 | -1
DROP TABLE stats;MySQL:/ 返回 DECIMAL,DIV 是整数商。
sql复制-- MySQL: / returns a DECIMAL with 4 extra digits; DIV is the integer quotient.
CREATE TABLE stats (id INT, hits INT, total INT);
INSERT INTO stats VALUES (1, 1, 3), (2, 7, 2), (3, 5, 0);
SELECT id, hits / NULLIF(total, 0) AS ratio FROM stats ORDER BY id;
-- => 1 | 0.3333
-- => 2 | 3.5000
-- => 3 | NULL
SELECT id, hits DIV NULLIF(total, 0) AS int_ratio FROM stats ORDER BY id;
-- => 1 | 0
-- => 2 | 3
-- => 3 | NULL
SELECT 7 / 2 AS slash, 7 DIV 2 AS div_pos, -7 DIV 2 AS div_neg;
-- => 3.5000 | 3 | -3
DROP TABLE stats;ClickHouse:/ 是 Float64,intDiv 是整数商。
sql复制-- ClickHouse: / always returns Float64; intDiv is the integer quotient (truncates toward zero).
CREATE TABLE stats (id INT, hits INT, total INT);
INSERT INTO stats VALUES (1, 1, 3), (2, 7, 2), (3, 5, 0);
SELECT id, ROUND(hits / NULLIF(total, 0), 4) AS ratio FROM stats ORDER BY id;
-- => 1 | 0.3333
-- => 2 | 3.5
-- => 3 | NULL
SELECT id, intDiv(hits, NULLIF(total, 0)) AS int_ratio FROM stats ORDER BY id;
-- => 1 | 0
-- => 2 | 3
-- => 3 | NULL
SELECT 7 / 2 AS slash, intDiv(7, 2) AS div_pos, intDiv(-7, 2) AS div_neg, toTypeName(7 / 2) AS slash_type;
-- => 3.5 | 3 | -3 | Float64
DROP TABLE stats;在四个引擎上都能原样运行的修复:先乘 100.0,除数用 NULLIF。
sql复制-- Portable fix: multiply by 100.0 first so the division is not between two integers.
CREATE TABLE stats (id INT, hits INT, total INT);
INSERT INTO stats VALUES (1, 1, 3), (2, 7, 2), (3, 5, 0);
SELECT id, ROUND(100.0 * hits / NULLIF(total, 0), 1) AS pct FROM stats ORDER BY id;
-- => 1 | 33.3
-- => 2 | 350
-- => 3 | NULL
DROP TABLE stats;平均值的平均(6)与整体平均(4)、加权平均(4)的对比,以及 NULL 对 AVG 的影响。
sql复制-- An average of group averages is not the overall average; AVG also skips NULL.
CREATE TABLE visits (id INT, region VARCHAR(10), seconds INT NULL);
INSERT INTO visits VALUES (1, 'east', 10), (2, 'west', 2), (3, 'west', 2), (4, 'west', 2), (5, 'west', NULL);
SELECT ROUND(AVG(a), 2) AS avg_of_avg FROM (SELECT region, AVG(seconds) AS a FROM visits GROUP BY region) r;
-- => 6
SELECT ROUND(AVG(seconds), 2) AS overall_avg FROM visits;
-- => 4
SELECT ROUND(SUM(s) * 1.0 / SUM(n), 2) AS weighted FROM (SELECT SUM(seconds) AS s, COUNT(seconds) AS n FROM visits GROUP BY region) r;
-- => 4
SELECT COUNT(*) AS rows_n, COUNT(seconds) AS seconds_n, ROUND(AVG(seconds), 2) AS avg_skip_null, ROUND(AVG(COALESCE(seconds, 0)), 2) AS avg_null_as_zero, ROUND(SUM(seconds) * 1.0 / COUNT(*), 2) AS sum_over_rows FROM visits;
-- => 5 | 4 | 4 | 3.2 | 3.2
DROP TABLE visits;PostgreSQL 与 MySQL 会扩宽 SUM 的类型,所以 2 x 2147483647 与 2^63 都放得下。
sql复制-- PostgreSQL and MySQL widen SUM: SUM of an INT is a bigint or decimal, SUM of a BIGINT is numeric or decimal.
CREATE TABLE big (id INT, n INT, b BIGINT);
INSERT INTO big VALUES (1, 2147483647, 9223372036854775807), (2, 2147483647, 1);
SELECT SUM(n) AS sum_int, SUM(b) AS sum_bigint FROM big;
-- => 4294967294 | 9223372036854775808
SELECT SUM(CAST(b AS DECIMAL(30, 0))) AS sum_decimal FROM big;
-- => 9223372036854775808
DROP TABLE big;SQLite:整数的 SUM 在 64 位以内是精确的(超出则报 integer overflow);total() 与 b + 1 会变成浮点数。
sql复制-- SQLite: SUM raises "integer overflow" past 64 bits; total() and mixed arithmetic fall back to REAL.
CREATE TABLE big (id INT, n INT, b BIGINT);
INSERT INTO big VALUES (1, 2147483647, 9223372036854775807), (2, 2147483647, 1);
SELECT SUM(n) AS sum_int, typeof(SUM(n)) AS sum_type FROM big;
-- => 4294967294 | integer
SELECT total(b) AS total_float, typeof(b + 1) AS plus_type, b + 1 AS plus_one FROM big WHERE id = 1;
-- => 9.223372036854776e+18 | real | 9.223372036854776e+18
DROP TABLE big;ClickHouse:Int64 的 SUM 会回绕成负数;用 Int128 扩宽。
sql复制-- ClickHouse: SUM of a 64-bit integer wraps around silently; widen the argument first.
CREATE TABLE big (id INT, n INT, b BIGINT);
INSERT INTO big VALUES (1, 2147483647, 9223372036854775807), (2, 2147483647, 1);
SELECT SUM(n) AS sum_int, toTypeName(SUM(n)) AS sum_type, SUM(b) AS wrapped, SUM(CAST(b AS Int128)) AS widened FROM big;
-- => 4294967294 | Int64 | -9223372036854775808 | 9223372036854775808
DROP TABLE big;DOUBLE 的求和有漂移(0.9999999999999999 不等于 1),DECIMAL 没有(PostgreSQL、MySQL、ClickHouse)。
sql复制-- Binary floating point cannot store 0.1 exactly; DECIMAL arithmetic is exact.
CREATE TABLE m (id INT, f DOUBLE PRECISION, d DECIMAL(10, 2));
INSERT INTO m VALUES (1, 0.1, 0.10), (2, 0.1, 0.10), (3, 0.1, 0.10), (4, 0.1, 0.10), (5, 0.1, 0.10), (6, 0.1, 0.10), (7, 0.1, 0.10), (8, 0.1, 0.10), (9, 0.1, 0.10), (10, 0.1, 0.10);
SELECT CASE WHEN SUM(f) = 1 THEN 'equal' ELSE 'different' END AS float_sum, CASE WHEN SUM(d) = 1 THEN 'equal' ELSE 'different' END AS decimal_sum FROM m;
-- => different | equal
SELECT CASE WHEN ABS(SUM(f) - 1) < 0.000001 THEN 'equal' ELSE 'different' END AS float_with_tolerance FROM m;
-- => equal
SELECT CASE WHEN CAST(0.1 AS DOUBLE PRECISION) + CAST(0.2 AS DOUBLE PRECISION) = CAST(0.3 AS DOUBLE PRECISION) THEN 'equal' ELSE 'different' END AS as_double, CASE WHEN CAST(0.1 AS DECIMAL(10, 2)) + CAST(0.2 AS DECIMAL(10, 2)) = CAST(0.3 AS DECIMAL(10, 2)) THEN 'equal' ELSE 'different' END AS as_decimal;
-- => different | equal
DROP TABLE m;SQLite:DECIMAL 其实是 REAL,只有整数分才是精确的。
sql复制-- SQLite has no exact decimal type: DECIMAL(10, 2) has NUMERIC affinity and stores 0.1 as a REAL. Use integer cents.
CREATE TABLE m (id INT, f DOUBLE PRECISION, d DECIMAL(10, 2), cents INT);
INSERT INTO m VALUES (1, 0.1, 0.10, 10), (2, 0.1, 0.10, 10), (3, 0.1, 0.10, 10), (4, 0.1, 0.10, 10), (5, 0.1, 0.10, 10), (6, 0.1, 0.10, 10), (7, 0.1, 0.10, 10), (8, 0.1, 0.10, 10), (9, 0.1, 0.10, 10), (10, 0.1, 0.10, 10);
SELECT CASE WHEN SUM(f) = 1 THEN 'equal' ELSE 'different' END AS float_sum, CASE WHEN SUM(cents) = 100 THEN 'equal' ELSE 'different' END AS cents_sum FROM m;
-- => equal | equal
SELECT CASE WHEN CAST(0.1 AS DOUBLE PRECISION) + CAST(0.2 AS DOUBLE PRECISION) = CAST(0.3 AS DOUBLE PRECISION) THEN 'equal' ELSE 'different' END AS as_double, CASE WHEN CAST(0.1 AS DECIMAL(10, 2)) + CAST(0.2 AS DECIMAL(10, 2)) = CAST(0.3 AS DECIMAL(10, 2)) THEN 'equal' ELSE 'different' END AS as_decimal, CASE WHEN 10 + 20 = 30 THEN 'equal' ELSE 'different' END AS as_cents;
-- => different | different | equal
DROP TABLE m;DOUBLE 列中的一半向偶数舍入,DECIMAL 列中向远离零舍入(PostgreSQL、MySQL、ClickHouse)。
sql复制-- Halves round to the nearest even number in a DOUBLE column but away from zero in a DECIMAL column.
CREATE TABLE r (id INT, f DOUBLE PRECISION, d DECIMAL(10, 3));
INSERT INTO r VALUES (1, 0.5, 0.5), (2, 1.5, 1.5), (3, 2.5, 2.5), (4, 3.5, 3.5), (5, -2.5, -2.5), (6, 2.675, 2.675);
SELECT id, ROUND(f) AS double_rounded, ROUND(d) AS decimal_rounded FROM r WHERE id < 6 ORDER BY id;
-- => 1 | 0 | 1
-- => 2 | 2 | 2
-- => 3 | 2 | 3
-- => 4 | 4 | 4
-- => 5 | -2 | -3
DROP TABLE r;保留两位小数:在 MySQL 与 ClickHouse 中,2.675 在两种列类型下都得到 2.68。
sql复制-- Rounding to places: 2.675 is stored as 2.67499999... in binary, yet these engines return 2.68 for the DOUBLE column too.
CREATE TABLE r (id INT, f DOUBLE PRECISION, d DECIMAL(10, 3));
INSERT INTO r VALUES (1, 0.5, 0.5), (2, 1.5, 1.5), (3, 2.5, 2.5), (4, 3.5, 3.5), (5, -2.5, -2.5), (6, 2.675, 2.675);
SELECT ROUND(f, 2) AS double_2dp, ROUND(d, 2) AS decimal_2dp FROM r WHERE id = 6;
-- => 2.68 | 2.68
DROP TABLE r;SQLite 对每个“一半”都向远离零舍入,2.675 变成 2.67。
sql复制-- SQLite rounds half away from zero for every value, and the "decimal" column is a REAL.
CREATE TABLE r (id INT, f DOUBLE PRECISION, d DECIMAL(10, 3));
INSERT INTO r VALUES (1, 0.5, 0.5), (2, 1.5, 1.5), (3, 2.5, 2.5), (4, 3.5, 3.5), (5, -2.5, -2.5), (6, 2.675, 2.675);
SELECT id, ROUND(f) AS double_rounded, ROUND(d) AS decimal_rounded FROM r WHERE id < 6 ORDER BY id;
-- => 1 | 1.0 | 1.0
-- => 2 | 2.0 | 2.0
-- => 3 | 3.0 | 3.0
-- => 4 | 4.0 | 4.0
-- => 5 | -3.0 | -3.0
SELECT ROUND(f, 2) AS double_2dp, ROUND(d, 2) AS decimal_2dp FROM r WHERE id = 6;
-- => 2.67 | 2.67
DROP TABLE r;以裸日期收尾的 BETWEEN 会丢掉最后一天(3 行里只数到 1);左闭右开区间数到 3。
sql复制-- A timestamp compared with a bare date means midnight at the start of that date.
CREATE TABLE events (id INT, ts TIMESTAMP);
INSERT INTO events VALUES (1, '2026-03-15 10:00:00'), (2, '2026-03-31 09:00:00'), (3, '2026-03-31 23:30:00'), (4, '2026-04-01 00:00:00'), (5, '2026-04-01 08:00:00');
SELECT COUNT(*) FROM events WHERE ts BETWEEN '2026-03-01' AND '2026-03-31';
-- => 1
SELECT COUNT(*) FROM events WHERE ts >= '2026-03-01' AND ts < '2026-04-01';
-- => 3
SELECT COUNT(*) FROM events WHERE ts >= '2026-04-01' AND ts < '2026-05-01';
-- => 2
DROP TABLE events;在 PostgreSQL、MySQL、ClickHouse 中,用下一个裸日期收尾还会数到那天的零点(4)。
sql复制-- A closing bound written as the next bare date also catches rows at exactly midnight of that date.
CREATE TABLE events (id INT, ts TIMESTAMP);
INSERT INTO events VALUES (1, '2026-03-15 10:00:00'), (2, '2026-03-31 09:00:00'), (3, '2026-03-31 23:30:00'), (4, '2026-04-01 00:00:00'), (5, '2026-04-01 08:00:00');
SELECT COUNT(*) FROM events WHERE ts BETWEEN '2026-03-01' AND '2026-04-01';
-- => 4
DROP TABLE events;PostgreSQL:同样的 timestamptz 行按 UTC 分为 1 和 2,按纽约时间分为 2 和 1。
sql复制-- The same three instants fall on different dates depending on the session time zone.
CREATE TABLE events (id INT, ts TIMESTAMPTZ);
SET TIME ZONE 'UTC';
INSERT INTO events VALUES (1, '2026-03-31 23:30:00'), (2, '2026-04-01 00:30:00'), (3, '2026-04-01 12:00:00');
SELECT CAST(ts AS DATE) AS d, COUNT(*) AS n FROM events GROUP BY 1 ORDER BY 1;
-- => 2026-03-31 | 1
-- => 2026-04-01 | 2
SET TIME ZONE 'America/New_York';
SELECT CAST(ts AS DATE) AS d, COUNT(*) AS n FROM events GROUP BY 1 ORDER BY 1;
-- => 2026-03-31 | 2
-- => 2026-04-01 | 1
-- Pin the zone in the query instead of relying on the session setting.
SELECT CAST(ts AT TIME ZONE 'UTC' AS DATE) AS d, COUNT(*) AS n FROM events GROUP BY 1 ORDER BY 1;
-- => 2026-03-31 | 1
-- => 2026-04-01 | 2
DROP TABLE events;MySQL:TIMESTAMP 在读取时会转换为会话 time_zone。
sql复制-- TIMESTAMP is stored as UTC and converted to the session time_zone on read.
CREATE TABLE events (id INT, ts TIMESTAMP);
SET time_zone = '+00:00';
INSERT INTO events VALUES (1, '2026-03-31 23:30:00'), (2, '2026-04-01 00:30:00'), (3, '2026-04-01 12:00:00');
SELECT DATE(ts) AS d, COUNT(*) AS n FROM events GROUP BY d ORDER BY d;
-- => 2026-03-31 | 1
-- => 2026-04-01 | 2
SET time_zone = '-04:00';
SELECT DATE(ts) AS d, COUNT(*) AS n FROM events GROUP BY d ORDER BY d;
-- => 2026-03-31 | 2
-- => 2026-04-01 | 1
DROP TABLE events;SQLite:显式平移文本时间戳;文本比较会排除裸日期收尾那天的零点(3 而不是 4)。
sql复制-- SQLite keeps timestamps as text with no zone; shift explicitly before taking the date.
CREATE TABLE events (id INT, ts TEXT);
INSERT INTO events VALUES (1, '2026-03-31 23:30:00'), (2, '2026-04-01 00:30:00'), (3, '2026-04-01 12:00:00');
SELECT date(ts) AS d, COUNT(*) AS n FROM events GROUP BY d ORDER BY d;
-- => 2026-03-31 | 1
-- => 2026-04-01 | 2
SELECT date(ts, '-4 hours') AS d, COUNT(*) AS n FROM events GROUP BY d ORDER BY d;
-- => 2026-03-31 | 2
-- => 2026-04-01 | 1
-- Text comparison: the midnight row 2026-04-01 00:00:00 sorts after the bare date 2026-04-01.
CREATE TABLE edge (id INT, ts TEXT);
INSERT INTO edge VALUES (1, '2026-03-15 10:00:00'), (2, '2026-03-31 09:00:00'), (3, '2026-03-31 23:30:00'), (4, '2026-04-01 00:00:00'), (5, '2026-04-01 08:00:00');
SELECT COUNT(*) FROM edge WHERE ts BETWEEN '2026-03-01' AND '2026-04-01';
-- => 3
DROP TABLE edge;
DROP TABLE events;ClickHouse:toDate 把时区作为参数。
sql复制-- toDate takes the time zone as an argument; without it the column time zone applies.
CREATE TABLE events (id INT, ts DateTime('UTC')) ENGINE = Memory;
INSERT INTO events VALUES (1, '2026-03-31 23:30:00'), (2, '2026-04-01 00:30:00'), (3, '2026-04-01 12:00:00');
SELECT toDate(ts) AS d, COUNT(*) AS n FROM events GROUP BY d ORDER BY d;
-- => 2026-03-31 | 1
-- => 2026-04-01 | 2
SELECT toDate(ts, 'America/New_York') AS d, COUNT(*) AS n FROM events GROUP BY d ORDER BY d;
-- => 2026-03-31 | 2
-- => 2026-04-01 | 1
DROP TABLE events;WHERE 会改变每个计数(cy 消失了);CASE 保留分母;HAVING 过滤整个分组。
sql复制-- WHERE removes rows before grouping (and changes every count); HAVING and CASE keep the groups whole.
CREATE TABLE orders (id INT, customer VARCHAR(10), status VARCHAR(10), total INT);
INSERT INTO orders VALUES (1, 'ann', 'paid', 50), (2, 'ann', 'refunded', 30), (3, 'bob', 'refunded', 20), (4, 'bob', 'refunded', 40), (5, 'cy', 'paid', 70), (6, 'cy', 'paid', 10);
SELECT customer, COUNT(*) AS refunded FROM orders WHERE status = 'refunded' GROUP BY customer ORDER BY customer;
-- => ann | 1
-- => bob | 2
SELECT customer, COUNT(*) AS n_orders, SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded, ROUND(100.0 * SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) / COUNT(*), 1) AS refund_pct FROM orders GROUP BY customer ORDER BY customer;
-- => ann | 2 | 1 | 50
-- => bob | 2 | 2 | 100
-- => cy | 2 | 0 | 0
SELECT customer, SUM(total) AS big_orders_total FROM orders WHERE total > 25 GROUP BY customer ORDER BY customer;
-- => ann | 80
-- => bob | 40
-- => cy | 70
SELECT customer, SUM(total) AS all_orders_total FROM orders GROUP BY customer HAVING SUM(total) > 60 ORDER BY customer;
-- => ann | 80
-- => cy | 80
DROP TABLE orders;