postgresql分页查询和OVER的用法

分页查询

分页查询需要返回指定范围的数据和总数据量，用下面的sql语句可以一次查询到。

select count(1) over() ,* from companies where pk>2 order by pk desc limit 3 offset 2

返回值是[{8,company1},{8,company2},{8,company3}]。
数据库一共10条数据，where条筛选出8条，limit后剩下3条。
可以看出执行顺序是where–>select–>limit，和以前的认识一样。

分组查询

根据不同的pk和状态分组，计算每组的数据条数。
distinct必须放在select项目的最前面，distinct a,b就是distinct a,distinct b的效果(distinct a,distinct b会报错)。
注意count(1)，count(left_table_pk)和count(right_table_pk)的区别：
count(1)始终大于0，因为如果没有数据就不会出现该条。
左连接时，如果left_table对应的right_table没有数据时，count(left_table_pk)大于0，count(right_table_pk)可能为0。

select DISTINCT mst_companies.company_pk,FOO.UK, FOO.STATUS1,
	COUNT(foo.uk) OVER(PARTITION BY mst_companies.company_pk,STATUS1) c1,COUNT(foo.uk) OVER(PARTITION BY UK) c2 from
mst_companies left join
	(SELECT USER_PK UK,
	(CASE WHEN study_start_dt IS NULL THEN 1 
	WHEN study_start_dt IS not NULL AND study_end_dt IS NULL THEN 2
	WHEN study_start_dt IS not NULL AND study_end_dt IS NOT NULL  AND TEST_start_dt IS NULL THEN 3
	WHEN study_start_dt IS not NULL AND study_end_dt IS NOT NULL  AND TEST_start_dt IS NOT NULL  AND TEST_END_dt IS NULL THEN 4
	WHEN study_start_dt IS not NULL AND study_end_dt IS NOT NULL  AND TEST_start_dt IS NOT NULL  AND TEST_END_dt IS NOT NULL THEN 5 END) AS STATUS1
            FROM trn_seminar_statuS) FOO on mst_companies.company_pk=foo.uk
order by company_pk desc,status1 asc