SELECT

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ FROM from_item [, ...] ]          --跳转到FROM子句
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]       --跳转到GROUP BY子句
[ HAVING condition [, ...] ]
[ WINDOW {window_name AS ( window_definition )} [, ...] ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
[ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ { [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] } | { LIMIT start, { count | ALL } } ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ]} [...] ];

WITH temp_t(name,isdba) AS (SELECT usename,usesuper FROM pg_user) SELECT * FROM temp_t;

WITH temp_t(name,isdba) AS MATERIALIZED (SELECT usename,usesuper FROM pg_user) SELECT * FROM temp_t;

WITH temp_t(name,isdba) AS NOT MATERIALIZED (SELECT usename,usesuper FROM pg_user)
 SELECT * FROM temp_t t1 WHERE name LIKE 'A%'
 UNION ALL
 SELECT * FROM temp_t t2 WHERE name LIKE 'B%';

SELECT DISTINCT(r_reason_sk) FROM tpcds.reason;

SELECT * FROM tpcds.reason LIMIT 1;

SELECT * FROM tpcds.reason LIMIT 1 OFFSET 2;

SELECT * FROM tpcds.reason LIMIT 2;

SELECT r_reason_desc FROM tpcds.reason ORDER BY r_reason_desc;

SELECT a.usename,b.locktime FROM pg_user a,pg_user_status b WHERE a.usesysid=b.roloid;

SELECT a.usename,b.locktime,a.usesuper FROM pg_user a FULL JOIN pg_user_status b on a.usesysid=b.roloid;

SELECT r_reason_id, AVG(r_reason_sk) FROM tpcds.reason GROUP BY r_reason_id HAVING AVG(r_reason_sk) > 25;

SELECT r_reason_id AS id FROM tpcds.reason GROUP BY id;

SELECT r_reason_sk, tpcds.reason.r_reason_desc
    FROM tpcds.reason
    WHERE tpcds.reason.r_reason_desc LIKE 'W%'
UNION
SELECT r_reason_sk, tpcds.reason.r_reason_desc
    FROM tpcds.reason
    WHERE tpcds.reason.r_reason_desc LIKE 'N%';

DROP TABLE IF EXISTS departments; 
CREATE TABLE departments
(id varchar(10),
pid varchar(10), 
dept_name varchar(50)); 
INSERT INTO  departments VALUES('0',null,'Head office'); 
INSERT INTO  departments VALUES('1','0','Technology');     -- 一级部门
INSERT INTO  departments VALUES('2','0','Marketing'); 
INSERT INTO  departments VALUES('3','0','HR'); 
INSERT INTO  departments VALUES('4','0','Business'); 
INSERT INTO  departments VALUES('5','0','Maintenance');  
INSERT INTO  departments VALUES('1-1','1','R&D Center');   -- 二级部门
INSERT INTO  departments VALUES('1-2','1','Pre-Research'); 
INSERT INTO  departments VALUES('1-3','1','Network'); 
INSERT INTO  departments VALUES('1-4','1','Test'); 
INSERT INTO  departments VALUES('1-5','1','Architecture'); 
INSERT INTO  departments VALUES('1-1-1','1-1','Frontend');   -- 三级部门
INSERT INTO  departments VALUES('1-1-2','1-1','Backend'); 
INSERT INTO  departments VALUES('1-1-3','1-1','Design'); 
INSERT INTO  departments VALUES('2-1','2','Branding'); 
INSERT INTO  departments VALUES('2-2','2','Pricing'); 
INSERT INTO  departments VALUES('2-3','2','Training');

SELECT * FROM departments;

WITH RECURSIVE  t1 AS (         --定义一个公共表达式名称为t1
SELECT id,pid,dept_name FROM departments WHERE id='1'   --初始化递归的根节点：查询id为1的记录，即技术部
UNION ALL         --递归合并结果（保留重复项）
SELECT            --递归部分：逐层向下查询
t2.id,            --子节点id，各一级部门名称
t2.pid,            --父节点id，即总公司
t2.dept_name          --子节点名称
FROM departments t2 
INNER JOIN t1 ON t2.pid = t1.id          --通过父节点ID连接递归结果
)
SELECT id,dept_name FROM t1 ORDER BY id;       --最终查询，按ID排序显示所有二、三级部门

WITH RECURSIVE  t1 AS ( 

SELECT    --初始化递归的根节点，查询id为1的记录，即技术部，显示转换为text类型
 id,
 pid,
 dept_name::text   --显式转换为text类型（为后续路径拼接做准备）
FROM departments 
WHERE id='1' 

UNION ALL 

SELECT    --递归部分：拼接层级路径
t2.id,
t2.pid,
(t1.dept_name||'->'||t2.dept_name)::text  AS dept_name   --拼接父级路径与当前节点名称，如Technology -> R&D Center
FROM departments t2 
INNER JOIN t1 ON t2.pid = t1.id ) 

SELECT id,dept_name FROM t1 ORDER BY id;   --最终查询，显示带层级路径的结果

CREATE TABLE tpcds.reason_p
(
  r_reason_sk integer,
  r_reason_id character(16),
  r_reason_desc character(100)
)
PARTITION BY RANGE (r_reason_sk)
(
  partition P_05_BEFORE values less than (05),
  partition P_15 values less than (15),
  partition P_25 values less than (25),
  partition P_35 values less than (35),
  partition P_45_AFTER values less than (MAXVALUE)
);

INSERT INTO tpcds.reason_p values(3,'AAAAAAAABAAAAAAA','reason 1'),(10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),(10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),(20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');

SELECT * FROM tpcds.reason_p PARTITION (P_05_BEFORE);
 r_reason_sk |   r_reason_id    |   r_reason_desc                   
-------------+------------------+------------------------------------
           4 | AAAAAAAABAAAAAAA | reason 3                          
           3 | AAAAAAAABAAAAAAA | reason 1                          
(2 rows)
——查询分区P_15的行数：
SELECT count(*) FROM tpcds.reason_p PARTITION (P_15);
 count  
--------
     3
(1 row)

SELECT COUNT(*),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id;
 count |   r_reason_id    
-------+------------------
     2 | AAAAAAAACAAAAAAA
     5 | AAAAAAAABAAAAAAA
(2 rows)

SELECT COUNT(*) c,r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id HAVING c>2;
 c |   r_reason_id    
---+------------------
 5 | AAAAAAAABAAAAAAA
(1 row)

SELECT COUNT(*),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id HAVING r_reason_id IN('AAAAAAAABAAAAAAA','AAAAAAAADAAAAAAA'); 
count |   r_reason_id    
-------+------------------
     5 | AAAAAAAABAAAAAAA
(1 row)

SELECT * FROM tpcds.reason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' INTERSECT SELECT * FROM tpcds.reason_p WHERE r_reason_sk<5;
 r_reason_sk |   r_reason_id    |     r_reason_desc                 
-------------+------------------+------------------------------------
           4 | AAAAAAAABAAAAAAA | reason 3                           
           3 | AAAAAAAABAAAAAAA | reason 1                           
(2 rows)

SELECT * FROM tpcds.reason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' EXCEPT SELECT * FROM tpcds.reason_p WHERE r_reason_sk<4;
r_reason_sk |   r_reason_id    |      r_reason_desc                  
-------------+------------------+------------------------------------
          10 | AAAAAAAABAAAAAAA | reason 2                          
          10 | AAAAAAAABAAAAAAA | reason 5                          
          10 | AAAAAAAABAAAAAAA | reason 4                          
           4 | AAAAAAAABAAAAAAA | reason 3                          
(4 rows)

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk  = t2.c_customer_sk(+) 
order by 1 desc limit 1;
 sr_item_sk | c_customer_id
------------+---------------
      18000 |
(1 row)

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk(+)  = t2.c_customer_sk 
order by 1 desc limit 1;
 sr_item_sk |  c_customer_id
------------+------------------
            | AAAAAAAAJNGEBAAA
(1 row)

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk  = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1 order by 1  limit 1;
 sr_item_sk | c_customer_id
------------+---------------
          1 |
(1 row)

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where not(t1.sr_customer_sk  = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1);
ERROR:  Operator "(+)" can not be used in nesting expression.
LINE 1: ...tomer_id from store_returns t1, customer t2 where not(t1.sr_...                                                           

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where (t1.sr_customer_sk  = t2.c_customer_sk(+))::bool;
ERROR:  Operator "(+)" can only be used in common expression.

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk(+)  = t2.c_customer_sk(+);
ERROR:  Operator "(+)" can't be specified on more than one relation in one join condition
HINT:  "t1", "t2"...are specified Operator "(+)" in one condition.

DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id int primary key,    --工号
name text,             --员工名
dep_1 text,            --一级部门
dep_2 text             --二级部门
);
 
INSERT INTO emp VALUES 
(01, 'liwei', 'Product', 'SRE'),
(02, 'liming', 'Product', 'U1'),
(03, 'hanlei', 'Product', 'U1'),
(04, 'hanmeimei', 'Product', 'SRE'),
(05, 'dingjun', 'Product', 'SRE'),
(06, 'huxue', 'Product', 'E1'),
(07, 'liuyi', 'Product', 'E1'),
(08, 'luhua', 'Product', 'E1'),
(09, 'yangjing', 'Product', 'U1'),
(10, 'binbin', 'Product', 'U1'),
(11, 'jim', 'Product', 'SRE'),
(12, 'leo', 'Product', 'SER');


SELECT COUNT(*), dep_2 FROM emp GROUP BY dep_2;   --统计二级部门的员工人数

SELECT COUNT(*), dep_2 FROM emp GROUP BY ROLLUP(dep_2) ORDER BY 1;