1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| CREATE TEMPORARY TABLE test (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
sort TINYINT ( 1 ) NOT NULL,
flag BIT(1) NOT NULL
);
INSERT INTO test (sort, flag) VALUE
(6, 0), (5, 1), (4, 0), (3, 1), (2, 0), (1, 1);
# 不同flag分别根据sort排序
SELECT *, row_number() OVER ( PARTITION BY flag ORDER BY sort) n FROM test;
# order写法
SELECT flag, sort FROM test ORDER BY flag, sort;
# 每个flag前两条数据 where和having后无法使用 窗口函数和窗口函数的别名
SELECT * FROM (SELECT *, row_number() OVER ( PARTITION BY flag ORDER BY sort) n FROM test) t WHERE n < 3;
# 每个flag sort的平均值
SELECT *, AVG(sort) OVER (PARTITION BY flag) n FROM test;
# group写法
SELECT flag, AVG(sort) FROM test GROUP BY flag;
DROP TABLE test;
|