postgreSQL中的row_number() 与distinct用法说明

select count (s.*)

from (

  select *, row_number () over (partition by fee_date order by fee_date) as gr

  from new_order where news_id= 'novel' and order_status= '2'

) s

where s.gr = 1

SELECT count ( DISTINCT fee_date) as dis from new_order where news_id= 'novel' and order_status= '2'

select *, row_number() over ( order by fee_date) from new_order

select *, row_number() over (partition by fee_date order by fee_date) as gr from new_order

SELECT DISTINCT 列名称 FROM 表名称

select DISTINCT fee_date from new_order

select DISTINCT fee_date,order_status from new_order

SELECT count ( DISTINCT fee_date) as dis from new_order where news_id= 'novel' and order_status= '2'

select id, distinct fee_date from new_order ; –会提示错误，因为 distinct 必须放在开头

SELECT

     *

FROM

     (

         SELECT

             tt.s_ci s_ci,

             sm.ci,

--          getdistance (

--              tt.longitude,

--              tt.latitude,

--              sm.longitude,

--              sm.latitude

--          ) distance,

             ROW_NUMBER () OVER (

                 PARTITION BY tt.s_ci

                 ORDER BY

                     getdistance (

                         tt.longitude,

                         tt.latitude,

                         sm.longitude,

                         sm.latitude

                     )

             ) rn

         FROM

             sm_cl_location sm

         INNER JOIN (

             SELECT

                 s_ci,

                 longitude,

                 latitude,

                 n3_pci,

                 n3_earfcn

             FROM

                 plan_ott_data

             WHERE

                 1 = 1

             AND (

                 s_ci = '460-00-1012286-2'

                 OR s_ci = '460-00-25514-130'

             )

             AND rpt_time BETWEEN '2018-04-30'

             AND '2018-05-29'

         ) tt ON sm.pci = tt.n3_pci

         AND sm.hannel_number = tt.n3_earfcn

     ) T

WHERE

     T .rn BETWEEN 1 and 3

ROW_NUMBER() OVER( [ PRITITION BY col1] ORDER BY col2[ DESC ] )

postgres=# create table student(id serial, name character varying ,course character varying ,score integer );

CREATE TABLE

postgres=#

postgres=# \d student

         Table "public.student"

  Column |  Type  |      Modifiers     

--------+-------------------+----------------------------------------------

  id  | integer    | not null default nextval( 'student_id_seq' ::regclass)

  name | character varying |

  course | character varying |

  score | integer    |

insert into student ( name ,course,score) values ( '周润发' , '语文' ,89);

insert into student ( name ,course,score) values ( '周润发' , '数学' ,99);

insert into student ( name ,course,score) values ( '周润发' , '外语' ,67);

insert into student ( name ,course,score) values ( '周润发' , '物理' ,77);

insert into student ( name ,course,score) values ( '周润发' , '化学' ,87);

insert into student ( name ,course,score) values ( '周星驰' , '语文' ,91);

insert into student ( name ,course,score) values ( '周星驰' , '数学' ,81);

insert into student ( name ,course,score) values ( '周星驰' , '外语' ,88);

insert into student ( name ,course,score) values ( '周星驰' , '物理' ,68);

insert into student ( name ,course,score) values ( '周星驰' , '化学' ,83);

insert into student ( name ,course,score) values ( '黎明' , '语文' ,85);

insert into student ( name ,course,score) values ( '黎明' , '数学' ,65);

insert into student ( name ,course,score) values ( '黎明' , '外语' ,95);

insert into student ( name ,course,score) values ( '黎明' , '物理' ,90);

insert into student ( name ,course,score) values ( '黎明' , '化学' ,78);

postgres=# select *,row_number() over( order by score desc )rn from student;

  id | name | course | score | rn

----+--------+--------+-------+----

  2 | 周润发 | 数学 | 99 | 1

  13 | 黎明 | 外语 | 95 | 2

  6 | 周星驰 | 语文 | 91 | 3

  14 | 黎明 | 物理 | 90 | 4

  1 | 周润发 | 语文 | 89 | 5

  8 | 周星驰 | 外语 | 88 | 6

  5 | 周润发 | 化学 | 87 | 7

  11 | 黎明 | 语文 | 85 | 8

  10 | 周星驰 | 化学 | 83 | 9

  7 | 周星驰 | 数学 | 81 | 10

  15 | 黎明 | 化学 | 78 | 11

  4 | 周润发 | 物理 | 77 | 12

  9 | 周星驰 | 物理 | 68 | 13

  3 | 周润发 | 外语 | 67 | 14

  12 | 黎明 | 数学 | 65 | 15

(15 rows )

postgres=# select *,row_number() over(partition by course order by score desc )rn from student;

  id | name | course | score | rn

----+--------+--------+-------+----

  5 | 周润发 | 化学 | 87 | 1

  10 | 周星驰 | 化学 | 83 | 2

  15 | 黎明 | 化学 | 78 | 3

  13 | 黎明 | 外语 | 95 | 1

  8 | 周星驰 | 外语 | 88 | 2

  3 | 周润发 | 外语 | 67 | 3

  2 | 周润发 | 数学 | 99 | 1

  7 | 周星驰 | 数学 | 81 | 2

  12 | 黎明 | 数学 | 65 | 3

  14 | 黎明 | 物理 | 90 | 1

  4 | 周润发 | 物理 | 77 | 2

  9 | 周星驰 | 物理 | 68 | 3

  6 | 周星驰 | 语文 | 91 | 1

  1 | 周润发 | 语文 | 89 | 2

  11 | 黎明 | 语文 | 85 | 3

(15 rows )

postgres=# select * from ( select *,row_number() over(partition by course order by score desc )rn from student)t where rn=1;

  id | name | course | score | rn

----+--------+--------+-------+----

  5 | 周润发 | 化学 | 87 | 1

  13 | 黎明 | 外语 | 95 | 1

  2 | 周润发 | 数学 | 99 | 1

  14 | 黎明 | 物理 | 90 | 1

  6 | 周星驰 | 语文 | 91 | 1

(5 rows )

postgres=# select * from ( select *,row_number() over(partition by course order by score)rn from student)t where rn=1;

  id | name | course | score | rn

----+--------+--------+-------+----

  15 | 黎明 | 化学 | 78 | 1

  3 | 周润发 | 外语 | 67 | 1

  12 | 黎明 | 数学 | 65 | 1

  9 | 周星驰 | 物理 | 68 | 1

  11 | 黎明 | 语文 | 85 | 1

(5 rows )