我有两个表:
表A
发票日期|客户id |发货id |重量|产品价格
表B
invoicedate|client_id|shipment_id|weight
我正在尝试创建一个新的表C,它使用sum(product_price(计算一个新列:所以我期待着这样的事情:(每个客户可以有多个具有相同shipment_id的发货,但product_price会发生变化对于每个不同的shipment_id((表B中缺少一些权重(
示例:表A
invoicedate | client_id | shipment_id | weight | product_price
12/10/19 1111 888 48 36
13/11/19 2222 111 30 45
12/10/19 1111 888 48 125
12/10/19 1111 888 48 127.2
表B
invoicedate | client_id | shipment_id | weight
12/10/19 1111 888 48
13/11/19 2222 111 30
12/10/19 1111 888 -
12/10/19 1111 888 48
新表C
distinct(client_id)|invoicedate | distinct(weight) | total_sum(product_price)
1111 | 12/10/19 | 1111 | (36+125+127.2)
2222 | 13/11/19 | 2222 | 45
我的代码:
create table C as
select A.invoicedate,A.shipment_id,A.weight,sum(A.product_price) as sum_product_price
from A
right join B on B.id=A.id
group by A.id, A.weight
总和(product_price(计算错误,我不明白为什么。。
select A.client_id,A.invoicedate,A.weight,SUM(product_price) from Table_A A
group by A.client_id,A.invoicedate ,A.weight
如果您想通过编译两个表来创建一个表。因此,本参考资料将对您有所帮助。
使用另一个表创建表
也可以使用CREATE TABLE
创建现有表的副本。
下面的SQL创建一个名为";TestTables";(这是"客户"表的副本(:
示例
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;
所以,在我按照说明尝试之后,我自己尝试了,我得到了
创建'C'
表:
create table C AS
SELECT A.client_id as distinct(client_id), A.invoicedate, B.weight AS distinct(weight), SUM(product_price) AS total_sum(product_price)
FROM A, B
FULL OUTER JOIN A.client_id = B.client_id
group by A.client_id, B.invoicedate;
选择声明:
SELECT * FROM C;
输出
distinct(client_id)|invoicedate | distinct(weight) | total_sum(product_price)
1111 | 12/10/19 | 1111 | 133
2222 | 13/11/19 | 2222 | 133
参考:SQL TABLE关键字