mysql - 工作中碰到的问题

1. 建立一张临时表，将多个表的内容暂时存储，然后再用这张表中的数据为条件去操作另外一张表。

注意每次执行此语句时，如果数据量很大，不建议使用。建议建立一张真实的表来存储。

with rr as (with t1 as (select a.receiving_code,a.product_barcode from odoo_ykd_oversea_shipping_information a 
inner join odoo_ykd_receiving_order_manage b on a.receiving_code=b.receiving_code and a.data_version=b.data_version inner join 
odoo_sync_control_scrapy d on d.data_version=b.data_version where d.finish_mark=0 and d.final_version_flag=1) 
select c.id from api_stock_picking_detail c inner join t1 on t1.product_barcode=c.sku and t1.receiving_code=c.carrier_no where c.state='draft') 
update api_stock_picking_detail set state='done',odoo_remark='sync_task' from rr where rr.id=api_stock_picking_detail.id

2. 查找出某一天，每一个odoo_warehouse_code最大id（最新）的一条记录

select a.data_version, a.product_barcode, a.rd_putaway_qty, a.rd_status, a.rd_update_time, a.receiving_code,
    a.odoo_warehouse_code, a.record_status from ykdcrawl_oversea_shipping_information a inner join ykdcrawl_task_control b on
    a.data_version=b.data_version inner join (select max(id) as id from ykdcrawl_task_control where data_date='%(data_date)s'
    and task_type=1 and result_type=1 and record_status=1 GROUP BY odoo_warehouse_code) c on b.id =c.id

3. 使用内连接时要注意，一对一，一对多关系的两张表建立出的数据不会多于数据量多的哪个表的总记录条数。

而多对多是要远远多余两张表的数据的，如果数据存在重复的情况，数量会成倍增加。

 

4. 添加普通索引

　　create index index_winitm_order_no on odoo_winit_inbound_order_detail_info(order_no)

　　格式：create index 索引名 on 表名（字段名）

 

 5.  新建表（postgresql)

create table "public"."odoo_amazon_fba_inbound_details"(
"id" serial,
"shipment_id" varchar(100),
"fulfillment_network_sku" varchar(200),
"quantity_shipped" integer,
"quantity_received" integer,
"serial_number" varchar(50),
"create_time" timestamp(6),
"update_time" timestamp(6),
"odoo_create_time" timestamp(6) DEFAULT ('now'::text)::timestamp(6) with time zone,
"odoo_update_time" timestamp(6) DEFAULT ('now'::text)::timestamp(6) with time zone
)
WITH (OIDS=FALSE)
;
ALTER TABLE "public"."odoo_amazon_fba_inbound_details" OWNER TO "odoo";

COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."shipment_id" IS '第三方单号';

COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."fulfillment_network_sku" IS 'sku';
COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."quantity_shipped" IS '预报数量';

COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."quantity_received" IS '实际收货数量';

COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."serial_number" IS '版本号';
COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."create_time" IS '被同步表的创建时间';
COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."update_time" IS '被同步表的更新时间';

COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."odoo_create_time" IS '创建时间';
COMMENT ON COLUMN "public"."odoo_amazon_fba_inbound_details"."odoo_update_time" IS '更新时间';