— 复杂逻辑表达式
SELECT
TRUE AND FALSE OR TRUE AS and_or,
TRUE AND (FALSE OR TRUE) AS and_paren_or,
NOT FALSE AND TRUE AS not_and,
NOT (FALSE AND TRUE) AS paren_not_and,
TRUE OR TRUE AND FALSE AS or_and_mix,
(TRUE OR TRUE) AND FALSE AS paren_or_and_mix;

执行结果：

and_or | and_paren_or | not_and | paren_not_and | or_and_mix | paren_or_and_mix
——–+————–+———+—————+————+——————
t | t | t | t | t | f
(1 row)

— 混合运算符优先级
SELECT
5 > 3 + 2 AS compare_arith,
(5 > 3) + 2 AS paren_compare,
10 * 2 > 15 AS arith_compare,
TRUE AND 5 > 3 AS logic_compare,
5 > 3 AND 2 < 4 OR 1 = 0 AS mixed_1, (5 > 3 AND 2 < 4) OR 1 = 0 AS mixed_2, 5 > 3 AND (2 < 4 OR 1 = 0) AS mixed_3;

执行结果：

学习交流加群风哥微信: itpux-com

compare_arith | paren_compare | arith_compare | logic_compare | mixed_1 | mixed_2 | mixed_3
—————+—————+—————+—————+———+———+———
f | 3 | t | t | t | t | t
(1 row)

— 创建测试表
CREATE TABLE fgedu_priority_test (
id SERIAL PRIMARY KEY,
status VARCHAR(20),
amount NUMERIC(10, 2),
category VARCHAR(20)
);

INSERT INTO fgedu_priority_test (status, amount, category) VALUES
(‘active’, 100.00, ‘A’),
(‘active’, 200.00, ‘B’),
(‘inactive’, 150.00, ‘A’),
(‘pending’, 300.00, ‘C’),
(‘active’, 50.00, ‘A’);

— 错误写法：想要status=active且category=A或B
— 错误理解：status = ‘active’ AND category = ‘A’ OR category = ‘B’
SELECT id, status, amount, category
FROM fgedu_priority_test
WHERE status = ‘active’ AND category = ‘A’ OR category = ‘B’;

执行结果（错误结果）：

id | status | amount | category
—-+———+———+———-
1 | active | 100.00 | A
2 | active | 200.00 | B
4 | pending | 300.00 | C
(3 rows)

— 正确写法：使用括号明确优先级
SELECT id, status, amount, category
FROM fgedu_priority_test
WHERE status = ‘active’ AND (category = ‘A’ OR category = ‘B’);

执行结果（正确结果）：

id | status | amount | category
—-+———+———+———-
1 | active | 100.00 | A
2 | active | 200.00 | B
(2 rows)

— NULL比较错误
SELECT
NULL = NULL AS null_eq,
NULL IS NULL AS null_is,
NULL != NULL AS null_neq,
NULL IS DISTINCT FROM NULL AS null_distinct,
NOT NULL = NULL AS not_null_eq,
NOT (NULL IS NULL) AS not_null_is;

执行结果：

null_eq | null_is | null_neq | null_distinct | not_null_eq | not_null_is
———+———+———-+—————+————-+————-
| t | | f | | f
(1 row)

— 类型转换优先级
SELECT
‘123’::INTEGER + 456 AS cast_first,
‘123’ || 456 AS concat_auto,
‘123’::TEXT || 456 AS cast_text_concat,
(‘123’::INTEGER + 456)::TEXT AS calc_then_cast;

执行结果：

cast_first | concat_auto | cast_text_concat | calc_then_cast
————+————-+——————+—————-
579 | 123456 | 123456 | 579
(1 row)

— 复杂业务条件查询
SELECT
id,
status,
amount,
category,
CASE
WHEN status = ‘active’ AND (category = ‘A’ OR category = ‘B’) AND amount >= 100
THEN ‘高优先级’
WHEN status = ‘active’ AND category = ‘C’
THEN ‘中优先级’
WHEN status = ‘pending’ AND amount > 200
THEN ‘待处理高金额’
ELSE ‘普通’
END AS priority_level
FROM fgedu_priority_test
ORDER BY id;

执行结果：

id | status | amount | category | priority_level
—-+———-+———+———-+—————-
1 | active | 100.00 | A | 高优先级
2 | active | 200.00 | B | 高优先级
3 | inactive | 150.00 | A | 普通
4 | pending | 300.00 | C | 待处理高金额
5 | active | 50.00 | A | 普通
(5 rows)

— 使用括号确保正确逻辑
SELECT
id,
status,
amount,
category,
CASE
WHEN (status = ‘active’ OR status = ‘pending’)
AND (category IN (‘A’, ‘B’))
AND (amount >= 100 OR amount <= 50) THEN '特殊处理' ELSE '正常处理' END AS process_type FROM fgedu_priority_test ORDER BY id;

执行结果：

from oracle:www.itpux.com

id | status | amount | category | process_type
—-+———-+———+———-+————–
1 | active | 100.00 | A | 特殊处理
2 | active | 200.00 | B | 正常处理
3 | inactive | 150.00 | A | 正常处理
4 | pending | 300.00 | C | 正常处理
5 | active | 50.00 | A | 特殊处理
(5 rows)

— 清理测试表
DROP TABLE IF EXISTS fgedu_priority_test;

执行结果：

风哥提示：

DROP TABLE