— 基本函数嵌套示例
SELECT
UPPER(TRIM(‘ hello world ‘)) AS nested_1,
ROUND(CAST(‘123.456’ AS NUMERIC), 2) AS nested_2,
LENGTH(REPLACE(‘PostgreSQL’, ‘SQL’, ”)) AS nested_3,
TO_CHAR(TO_DATE(‘20260404’, ‘YYYYMMDD’), ‘YYYY年MM月DD日’) AS nested_4;

执行结果：

学习交流加群风哥QQ113257174

nested_1 | nested_2 | nested_3 | nested_4
—————+———-+———-+—————–
HELLO WORLD | 123.46 | 8 | 2026年04月04日
(1 row)

— 多层函数嵌套
SELECT
UPPER(SUBSTRING(TRIM(‘ PostgreSQL Database ‘) FROM 1 FOR 10)) AS multi_nested_1,
ROUND(ABS(SIN(RADIANS(90))), 4) AS multi_nested_2,
COALESCE(NULLIF(TRIM(‘ ‘), ”), ‘空值’) AS multi_nested_3,
TO_CHAR(DATE_TRUNC(‘month’, CURRENT_DATE + INTERVAL ‘1 month’), ‘YYYY-MM-DD’) AS multi_nested_4;

执行结果：

风哥提示：

multi_nested_1 | multi_nested_2 | multi_nested_3 | multi_nested_4
—————-+—————-+—————-+—————-
POSTGRESQL | 1.0000 | 空值 | 2026-05-01
(1 row)

— 运算符优先级演示
SELECT
2 + 3 * 4 AS priority_1,
(2 + 3) * 4 AS priority_2,
10 / 2 + 3 AS priority_3,
10 / (2 + 3) AS priority_4,
TRUE OR FALSE AND FALSE AS priority_5,
(TRUE OR FALSE) AND FALSE AS priority_6;

执行结果：

更多视频教程www.fgedu.net.cn

priority_1 | priority_2 | priority_3 | priority_4 | priority_5 | priority_6
————+————+————+————+————+————
14 | 20 | 8 | 2 | t | f
(1 row)

— 类型转换与表达式
SELECT
‘100’::INTEGER + 50 AS int_add,
‘3.14’::NUMERIC * 2 AS num_mul,
‘2026-04-04’::DATE + 7 AS date_add,
123::TEXT || ‘abc’ AS text_concat,
TRUE::INTEGER AS bool_to_int,
123.456::INTEGER AS float_to_int;

执行结果：

int_add | num_mul | date_add | text_concat | bool_to_int | float_to_int
———+———+————-+————-+————-+————–
150 | 6.28 | 2026-04-11 | 123abc | 1 | 123
(1 row)

— CASE表达式嵌套
SELECT
product_name,
price,
CASE
WHEN category = ‘电子产品’ THEN
CASE
WHEN price > 1000 THEN ‘高端电子’
WHEN price > 100 THEN ‘中端电子’
ELSE ‘低端电子’
END
WHEN category = ‘家具’ THEN
CASE
WHEN price > 500 THEN ‘高端家具’
ELSE ‘普通家具’
END
ELSE ‘其他’
END AS product_level
FROM (VALUES
(‘笔记本电脑’, 5999.00, ‘电子产品’),
(‘无线鼠标’, 99.00, ‘电子产品’),
(‘机械键盘’, 399.00, ‘电子产品’),
(‘办公椅’, 899.00, ‘家具’),
(‘书架’, 299.00, ‘家具’)
) AS products(product_name, price, category);

执行结果：

更多学习教程公众号风哥教程itpux_com学习交流加群风哥微信: itpux-com

product_name | price | product_level
————–+——–+—————
笔记本电脑 | 5999.00| 高端电子
无线鼠标 | 99.00| 低端电子
机械键盘 | 399.00| 中端电子
办公椅 | 899.00| 高端家具
书架 | 299.00| 普通家具
(5 rows)

— 函数与CASE组合
SELECT
customer_name,
total_amount,
CASE
WHEN total_amount >= 10000 THEN
ROUND(total_amount * 0.85, 2)
WHEN total_amount >= 5000 THEN
ROUND(total_amount * 0.90, 2)
WHEN total_amount >= 1000 THEN
ROUND(total_amount * 0.95, 2)
ELSE total_amount
END AS discounted_amount,
COALESCE(
CASE
WHEN total_amount >= 10000 THEN ‘VIP折扣15%’
WHEN total_amount >= 5000 THEN ‘金卡折扣10%’
WHEN total_amount >= 1000 THEN ‘银卡折扣5%’
ELSE NULL
END,
‘无折扣’
) AS discount_level
FROM (VALUES
(‘风哥1号’, 15000.00),
(‘风哥2号’, 8000.00),
(‘王五’, 3000.00),
(‘赵六’, 500.00)
) AS customers(customer_name, total_amount);

执行结果：

customer_name | total_amount | discounted_amount | discount_level
—————+————–+——————-+—————-
风哥1号 | 15000.00 | 12750.00 | VIP折扣15%
风哥2号 | 8000.00 | 7200.00 | 金卡折扣10%
王五 | 3000.00 | 2850.00 | 银卡折扣5%
赵六 | 500.00 | 500.00 | 无折扣
(4 rows)

— 创建测试表
CREATE TABLE fgedu_orders_calc (
order_id SERIAL PRIMARY KEY,
customer_name VARCHAR(50),
product_name VARCHAR(100),
quantity INTEGER,
unit_price NUMERIC(10, 2),
discount_rate NUMERIC(3, 2),
order_date DATE DEFAULT CURRENT_DATE
);

INSERT INTO fgedu_orders_calc (customer_name, product_name, quantity, unit_price, discount_rate) VALUES
(‘风哥1号’, ‘产品A’, 10, 100.00, 0.10),
(‘风哥2号’, ‘产品B’, 20, 50.00, 0.05),
(‘王五’, ‘产品A’, 15, 100.00, 0.15),
(‘风哥1号’, ‘产品C’, 5, 200.00, 0.10),
(‘风哥2号’, ‘产品A’, 8, 100.00, 0.08);

— 使用CTE简化复杂计算
WITH order_calc AS (
SELECT
order_id,
customer_name,
product_name,
quantity,
unit_price,
discount_rate,
quantity * unit_price AS subtotal,
quantity * unit_price * discount_rate AS discount_amount
FROM fgedu_orders_calc
),
order_final AS (
SELECT
*,
subtotal – discount_amount AS final_amount,
ROUND((discount_amount / subtotal) * 100, 2) AS discount_percent
FROM order_calc
)
SELECT
customer_name,
COUNT(*) AS order_count,
SUM(subtotal) AS total_subtotal,
SUM(discount_amount) AS total_discount,
SUM(final_amount) AS total_final,
ROUND(AVG(discount_percent), 2) AS avg_discount_percent
FROM order_final
GROUP BY customer_name
ORDER BY total_final DESC;

执行结果：

from oracle:www.itpux.com

customer_name | order_count | total_subtotal | total_discount | total_final | avg_discount_percent
—————+————-+—————-+—————-+————-+———————-
风哥1号 | 2 | 2000.00 | 200.00 | 1800.00 | 10.00
风哥2号 | 2 | 1400.00 | 90.00 | 1310.00 | 6.50
王五 | 1 | 1500.00 | 225.00 | 1275.00 | 15.00
(3 rows)

— LATERAL表达式示例
SELECT
c.customer_name,
c.total_orders,
top_products.product_list
FROM (
SELECT
customer_name,
COUNT(*) AS total_orders,
SUM(quantity * unit_price) AS total_amount
FROM fgedu_orders_calc
GROUP BY customer_name
) c
CROSS JOIN LATERAL (
SELECT STRING_AGG(oc.product_name, ‘, ‘ ORDER BY oc.quantity DESC) AS product_list
FROM fgedu_orders_calc oc
WHERE oc.customer_name = c.customer_name
) top_products
ORDER BY c.total_amount DESC;

执行结果：

customer_name | total_orders | product_list
—————+————–+———————————
风哥1号 | 2 | 产品A, 产品C
风哥2号 | 2 | 产品B, 产品A
王五 | 1 | 产品A
(3 rows)

— LATERAL计算每客户最贵订单
SELECT
c.customer_name,
c.order_id,
c.product_name,
c.final_amount,
max_order.max_amount
FROM (
SELECT
customer_name,
order_id,
product_name,
quantity * unit_price * (1 – discount_rate) AS final_amount
FROM fgedu_orders_calc
) c
CROSS JOIN LATERAL (
SELECT MAX(quantity * unit_price * (1 – discount_rate)) AS max_amount
FROM fgedu_orders_calc oc
WHERE oc.customer_name = c.customer_name
) max_order
WHERE c.final_amount = max_order.max_amount
ORDER BY c.customer_name;

执行结果：

customer_name | order_id | product_name | final_amount | max_amount
—————+———-+————–+————–+————
风哥1号 | 1 | 产品A | 900.00 | 900.00
风哥2号 | 2 | 产品B | 950.00 | 950.00
王五 | 3 | 产品A | 1275.00 | 1275.00
(3 rows)

— 综合计算案例：销售提成计算
WITH fgfgfgfgsales_data AS (
SELECT
o.customer_name,
o.product_name,
o.quantity,
o.unit_price,
o.discount_rate,
o.quantity * o.unit_price AS gross_amount,
o.quantity * o.unit_price * (1 – o.discount_rate) AS net_amount,
EXTRACT(MONTH FROM o.order_date) AS sale_month
FROM fgedu_orders_calc o
),
commission_calc AS (
SELECT
customer_name,
sale_month,
SUM(net_amount) AS monthly_fgfgfgfgsales,
CASE
WHEN SUM(net_amount) >= 2000 THEN 0.10
WHEN SUM(net_amount) >= 1000 THEN 0.08
WHEN SUM(net_amount) >= 500 THEN 0.05
ELSE 0.03
END AS commission_rate
FROM fgfgfgfgsales_data
GROUP BY customer_name, sale_month
)
SELECT
customer_name,
sale_month,
monthly_fgfgfgfgsales,
commission_rate,
ROUND(monthly_fgfgfgfgsales * commission_rate, 2) AS commission,
ROUND(
monthly_fgfgfgfgsales * commission_rate /
NULLIF(SUM(monthly_fgfgfgfgsales) OVER (), 0) * 100,
2
) AS commission_percent
FROM commission_calc
ORDER BY monthly_fgfgfgfgsales DESC;

执行结果：

customer_name | sale_month | monthly_fgfgfgfgsales | commission_rate | commission | commission_percent
—————+————+—————+—————–+————+——————–
风哥1号 | 4 | 1800.00 | 0.08 | 144.00 | 36.89
王五 | 4 | 1275.00 | 0.08 | 102.00 | 26.13
风哥2号 | 4 | 1310.00 | 0.08 | 104.80 | 26.84
(3 rows)

— 删除测试表
DROP TABLE IF EXISTS fgedu_orders_calc;

执行结果：

DROP TABLE