背景介绍:
有两张表:busi_customer用于保存客户信息;busi_order用于保存客户的订单信息;
建表语句如下:
CREATE TABLE "public"."busi_customer" (
"id" int8 NOT NULL DEFAULT nextval('busi_customer_id_seq'::regclass),
"cust_code" varchar(255) COLLATE "pg_catalog"."default",
"cust_name" varchar(255) COLLATE "pg_catalog"."default",
CONSTRAINT "busi_customer_pkey" PRIMARY KEY ("id")
);
CREATE TABLE "public"."busi_order" (
"id" int8 NOT NULL DEFAULT nextval('busi_order_id_seq'::regclass),
"cust_code" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"amount" numeric(10,2) NOT NULL,
"order_number" text COLLATE "pg_catalog"."default",
CONSTRAINT "busi_order_pkey" PRIMARY KEY ("id")
);
需求:请统计不同用户的累计订单金额。
两张表中分别插入数据:
busi_customer
busi_order
统计SQL:
select o.cust_code,sum(amount) as amonut
from busi_order o
left join
busi_customer c on o.cust_code = c.cust_code
group by o.cust_code;
统计结果:
现在在busi_customer表中插入一条cust_code为A001的数据后,用户表的结果如下:
再执行统计SQL,发现A001的订单金额会重复计算: