B2Bsql备注

查询子品与主表是否相等

SELECT * FROM yx_store_product WHERE id not in (SELECT product_id FROM yx_store_product_attr_value )

B2B上线清空测试数据

-- 删除回传订单
DELETE FROM sm_order_deliver_detail
-- 清空购物车
DELETE FROM yx_store_cart;
-- 拼团品
DELETE FROM yx_store_combination;
-- 优惠券
DELETE FROM yx_store_coupon;
DELETE FROM yx_store_coupon_issue;
DELETE FROM yx_store_coupon_issue_user;
DELETE FROM yx_store_coupon_product;
DELETE FROM yx_store_coupon_user;
-- 删除订单
DELETE FROM yx_store_order;
DELETE FROM yx_store_order_cart_info;
DELETE FROM yx_store_order_status;
-- 秒杀品
DELETE FROM yx_store_seckill;
-- 用户充值表
DELETE FROM yx_user_recharge;
-- 用户账单表
DELETE FROM yx_user_bill;
-- 用户签到记录表
DELETE FROM yx_user_sign;

视图 v_app_good_attr

select `zt_product_info`.`product_id` AS `产品编码`,`zt_product_info`.`factory_name` AS `生产厂家`,`zt_product_info`.`product_name` AS `商品名称`,`zt_product_info`.`common_name` AS `产品名称`,`zt_product_info`.`validity_date` AS `有效期`,`zt_product_info`.`approval_no` AS `批准文号`,`zt_product_info`.`spec` AS `规格`,`zt_product_info`.`dosage` AS `剂型`,`zt_product_info`.`gsp_type` AS `GSP分类`,`zt_product_info`.`unit` AS `单位`,`zt_product_info`.`special_sort` AS `特殊分类`,`zt_product_info`.`packaging_quantity` AS `包装量`,`zt_product_info`.`medium_packaging_quantity` AS `中包量`,`zt_product_info`.`effect` AS `功能主治`,`zt_product_info`.`quality_standard` AS `质量标准`,`zt_product_info`.`storage_condition` AS `存储条件`,if((`zt_product_info`.`cold_storage_type` = 1),'是','否') AS `冷藏药品`,if((`zt_product_info`.`is_insulin` = 1),'是','否') AS `胰岛素标识`,'否' AS `计生用品`,if((`zt_product_info`.`prescription_sales_type` = 1),'是','否') AS `凭处方销售`,if(`zt_product_info`.`imported_type`,'是','否') AS `进口标志`,if((`zt_product_info`.`external` = 1),'是','否') AS `外用药`,if((`zt_product_info`.`injection` = 1),'是','否') AS `注射剂`,rtrim(`zt_product_info`.`license_legal_person`) AS `药品上市许可持有人` from `zt_product_info`

视图 v_order_item

select `yoi`.`cart_id` AS `id`,`yoi`.`unique` AS `unique`,`yoi`.`order_id` AS `orderId`,json_extract(`yoi`.`cart_info`,'$.payPrice') AS `payPrice`,json_extract(`yoi`.`cart_info`,'$.isCoupon') AS `isCoupon`,json_extract(`yoi`.`cart_info`,'$.productId') AS `productId`,json_extract(`yoi`.`cart_info`,'$.cartNum') AS `cartNum`,json_extract(`yoi`.`cart_info`,'$.seckillId') AS `seckillId`,json_extract(`yoi`.`cart_info`,'$.combinationId') AS `combinationId`,json_extract(`yoi`.`cart_info`,'$.bargainId') AS `bargainId`,trim(both '"' from json_extract(`yoi`.`cart_info`,'$.productAttrUnique')) AS `productAttrUnique`,json_extract(`yoi`.`cart_info`,'$.truePrice') AS `truePrice` from `yx_store_order_cart_info` `yoi`

视图v_user_customer

select `u`.`uid` AS `uid`,`u`.`status` AS `isAuth`,`u`.`is_default` AS `is_default`,`u`.`company_id` AS `company_id`,`ua`.`is_disabled` AS `isDisabled`,`ua`.`customer_code` AS `companyCode`,`ua`.`company_name` AS `name`,`ua`.`p_customer_code` AS `pCustomerCode`,`ua`.`is_disabled` AS `cIsDisabled`,`ua`.`customer_type` AS `isBigCustomer`,ifnull(`ua`.`is_del`,0) AS `isDel`,`r`.`rate` AS `rate` from ((`yx_company_user` `u` left join `yx_company_auth` `ua` on((`u`.`company_id` = `ua`.`id`))) left join `company_increase_rate` `r` on((`ua`.`customer_group` = `r`.`name`)))