PgSQL汇总

来源：化拓教育网

SQL==

sql执行顺序

1.from
2.on
3.join
4.where
5.group by(开始使用select中的别名，后面的语句中都可以使用)
6. avg,sum..
7.having
8.select
9.distinct
10.order by
11.limit

⭐流程控制语句

CASE

写法一：

CASE case_value
	WHEN when_value THEN
		statement_list
	ELSE
		statement_list
END CASE;

写法二：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result_default
END

举例：

"GoodQty1" =CASE WHEN T1."U_QuantityType" = '良品' THEN	T1."Quantity" ELSE TT."GoodQty1" 	END,
"BadQty1" = CASE WHEN T1."U_QuantityType" = '不良' THEN	T1."Quantity" ELSE TT."BadQty1" 	END,

FOR

FOR var IN query LOOP
	statement_list
END LOOP;

-- 示例
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id, name FROM users WHERE active = true LOOP
        RAISE NOTICE 'Active user: id=%, name=%', rec.id, rec.name;
    END LOOP;
END $$;

IF…ELSE

IF search_condition THEN
	statement_list
ELSE
	statement_list
END IF;

LOOP

LOOP
	statement_list

	IF exit_condition THEN
		EXIT; 
	END IF; 
END LOOP;

WHILE

WHILE search_condition LOOP
	statement_list
END LOOP;

$单据状态显示

一般不用下面代码，窗口设置——数据源——自定义项就好了。

SELECT
	"DocStatus",
    CASE
        WHEN "DocStatus" = 'O' THEN '未清'
        WHEN "DocStatus" = 'Q' THEN '已取消'
        WHEN "DocStatus" = 'C' THEN '已清'
        ELSE '未知状态'
    END AS status_description
FROM
    "U_AAB_OCDB";

或者这样写：

select 
	case "Sex"
	when '1' then '男'
	when '2' then '女'
	end as sex
from "U_AAB_OSTU"

$总金额

select @Price*@Num AS "Total"

$自定义下拉类型数据

SELECT '水果' AS type
UNION ALL
SELECT '家具' AS type
UNION ALL
SELECT '电器' AS type;

$日期

$查询周内数据

-- 一周内
AND "Date" >= TO_CHAR(CURRENT_DATE - INTERVAL '7 days', 'YYYYMMDD')
AND "Date" <= TO_CHAR(CURRENT_DATE, 'YYYYMMDD')
    
    
-- 本周内（右侧日期为text类型）
LEFT("TestTime", 8) >= TO_CHAR(DATE_TRUNC('week', CURRENT_DATE), 'YYYYMMDD')
AND LEFT("TestTime", 8) < TO_CHAR(DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week', 'YYYYMMDD')


  -- 本周内（右侧日期为TIMESTAM写法）
  AND "DateTime" >= TO_TIMESTAMP(TO_CHAR(DATE_TRUNC('week', CURRENT_DATE), 'YYYYMMDD'), 'YYYYMMDD')
  AND "DateTime" < TO_TIMESTAMP(TO_CHAR(DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week', 'YYYYMMDD'), 'YYYYMMDD')
  
-- 本周内（右侧日期为Date类型）
AND t0."Time" >= DATE_TRUNC('week', CURRENT_DATE)
AND t0."Time" < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week'

$近一周近一月

--根据下次计划审核日期来，增加一个筛选，近一周，近一个月这种筛选 
AND (
	( @TimeFilter = '近一周' AND t1."NextDate" <= CURRENT_DATE + INTERVAL '7 days' ) 
	OR ( @TimeFilter = '近一个月' AND t1."NextDate" <= CURRENT_DATE + INTERVAL '1 month' ) 
	OR ( @TimeFilter IS NULL OR @TimeFilter = '' ) -- 添加默认情况

)

$格式化日期

获取天数差

select '2024-11-18 12:11:33'::date - '2024-11-13 12:11:33'::date

获取小时差

-- 小时差 = 24h内的小时差 + 天数差*24h
SELECT EXTRACT(HOUR FROM age('2024-11-14 12:11:33'::timestamp, '2024-11-13 9:11:33'::timestamp)) -- 获取24h内的小时差
       + EXTRACT(DAY FROM age('2024-11-14 12:11:33'::timestamp, '2024-11-13 9:11:33'::timestamp)) * 24; -- 获取天数差*24h

date_trunc()

date_trunc(unit, timestamp)

其中，参数解释如下：
unit
：指定截断的单位，可以是以下之一：

'microseconds', 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'
timestamp：要截断的日期或时间戳。
示例用法：

通过 date_trunc() 函数，可以方便地将日期或时间戳按照指定精度截断，返回一个新的日期或时间戳。

SELECT
	"StartTime",
	"EndTime",
	"Place" 
FROM
	"U_ALF_OARR1" 
WHERE
	"StartTime" > date_trunc ( 'day', CURRENT_DATE ) 
	AND "StartTime" < date_trunc ('day',CURRENT_DATE + INTERVAL '2 day')

TO_CHAR ()

要将日期格式化为“年月日时分秒”，但是结果是字符类型。

TO_CHAR(T1."CreatDate", 'YYYY.MM.DD HH24:MI:SS')

TO_CHAR ( "CreatDate", 'YYYY.MM.DD' )

SELECT
	*,
	TO_CHAR ( "CreatDate", 'YYYY.MM.DD' ) AS FormattedCreatDate 
FROM
	"U_AAB_OCDB1" T1 
WHERE
	"CreatDate" BETWEEN '2023-12-01' 
	AND '2024-03-01';

日期格式化可以根据不同的需求和编程语言进行灵活选择。以下是一些常见的日期格式化示例：
'HH24:MI:SS'：15:20:09 24小时时间制
ISO 8601 标准格式：

YYYY-MM-DD：例如 2024-06-25
YYYY-MM-DDTHH:mm:ss：带时间的ISO 8601格式，例如 2024-06-25T14:30:00
美国标准日期格式：

MM/DD/YYYY：例如 06/25/2024
MM/DD/YYYY HH:mm:ss：带时间的格式，例如 06/25/2024 14:30:00
欧洲标准日期格式：

DD/MM/YYYY：例如 25/06/2024
DD/MM/YYYY HH:mm:ss：带时间的格式，例如 25/06/2024 14:30:00
RFC 2822 格式：

D, d M YYYY HH:mm:ss ZZ：例如 Tue, 25 Jun 2024 14:30:00 +0000
自定义格式（具体格式可以根据需要自由定义）：

YYYY年MM月DD日 HH时mm分ss秒：例如 2024年06月25日 14时30分00秒
在实际开发中，根据不同的需求选择合适的日期格式是很重要的。大多数编程语言和数据库系统提供了丰富的日期格式化函数和工具，以满足不同的格式化需求。

$序号生成

在模板中常在表格最左侧使用“序号”字段，此时，可以借助窗口函数生成序号1，2，3，…

窗口函数

排序

核心代码：

ROW_NUMBER() OVER (ORDER BY T1."LineNum", T0."DocEntry", T0."BrchId" ASC) AS "LineNum",

例如：

SELECT
    ROW_NUMBER() OVER (ORDER BY T1."LineNum", T0."DocEntry", T0."BrchId" ASC) AS "LineNum",
    T0."DocCode",
    T0."CreateDate",
    T0."Address",
    T1."Price",
    T1."Person",
    T1."LineNum"
FROM
    "U_AAB_OCDB1" T1
    FULL JOIN "U_AAB_OCDB" T0 ON T0."DocEntry" = T1."DocEntry";

这段 SQL 代码使用了窗口函数 ROW_NUMBER()，并通过 OVER 子句定义了其作用范围和排序规则。让我来解释：

ROW_NUMBER(): 这是一种窗口函数，用于为结果集中的每一行分配一个唯一的序号（行号），序号的分配是基于指定的排序顺序。
OVER (ORDER BY …): 这个子句定义了 ROW_NUMBER() 函数的操作范围。具体来说：
ORDER BY T1."TransType", T0."DocEntry", T0."LineNum"：这部分指定了排序的顺序。首先按照 T2."TransType" 字段升序排列，然后按照 T0."DocEntry" 字段升序排列，最后按照 T0."LineNum" 字段升序排列。

AS “LineNum”: 这部分为通过 ROW_NUMBER() 生成的行号（序号）指定了一个别名 “LineNum”。在查询的结果中，可以使用 "LineNum" 这个别名来引用这个生成的列。

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文