PostgreSQL常见SQL查询

1.现有表A如下数据, 表字段 Id, member_id,receiver,status,amount,month; receiver字段为JSONB类型

id	member_id	receiver	status	amount	month
1	1000	{“name”: “张三”, “account”: “13100000000”}	1	22.22	2022-01
2	1001	{“name”: “李四”, “account”: “13100000001”}	2	11.11	2022-02
3	1002	{“name”: “王五”, “account”: “13100000002”}	3	33.33	2022-03

(1) 需要单独列出name,account

SELECT (receiver ->> 'name') name, (receiver ->> 'account') account FROM 表A 

(2) 需要查询name为"张三"的数据

SELECT id,receiver FROM 表A WHERE receiver::JSONB @> '{"name":'张三'}'::jsonb

(3) 需要查询account为"13100000001"的数据

SELECT id,receiver FROM 表A WHERE receiver::JSONB @> '{"account":'13100000001'}'::jsonb

(3) 需要统计其中几个状态的总金额

SELECT COALESCE(SUM(amount) FILTER (WHERE status= 1), 0.00) fristAmount,
       COALESCE(SUM(amount) FILTER (WHERE status= 2), 0.00) twoAmount
FROM 表A

(4) 需要统计某月的总金额

SELECT COALESCE(SUM(amount) FILTER (WHERE month= '2022-01'), 0.00) monthAmount FROM 表A

(5) 表A的member_id建立唯一索引,如做新增或者修改操作

INSET INTO 表A AS aa (member_id, receiver, status, amount, month)
VALUES (#{memberId}, #{receiver,typeHandler=***.JSONHandler},#{status},#{amount},#{month})
ON CONFLICT(member_id)
DO UPDATE SET receiver= #{receiver,typeHandler=***.JSONHandler},
              status = #{status},
              amount= #{amount}
WHERE aa.member_id = #{memberId}