--按店铺分类统计订单状态select sum(A) as 已付款 ,sum(B) as 未付款,C as 店铺名from( select case when status_id=2 then 1 else 0 end AS A , case when status_id=4 then 1 else 0 end AS B, shop_id AS C from t_xs_trade where status_id in(2,4)) group by C;
结果：
| 已付款 | 未付款 | 店铺名 | |
| 1 | 62 | 5 | shop04 |
| 2 | 173 | 229 | shop02 |
| 3 | 1 | 1 | shop08 |
| 4 | 27 | 0 | shop01 |
| 5 | 3 | 0 | shop06 |
| 6 | 103 | 38 | 855006870 |
表结构：
TID VARCHAR2(30) N 订单编号SHOP_ID VARCHAR2(30) Y 店铺IDSHOP_NAME VARCHAR2(50) Y 店铺名称PT_ID VARCHAR2(10) Y 平台IDSTATUS_ID VARCHAR2(35) Y 订单状态IDFROM_ID VARCHAR2(30) Y 来源单号SHIP_ID VARCHAR2(30) Y 物流单号TYPE VARCHAR2(100) Y 交易类型列表BUYER_ID VARCHAR2(20) Y 买家账号BUYER_NAME VARCHAR2(50) Y 买家名称NUM NUMBER(6) Y 商品数量PAYMENT_TIME VARCHAR2(50) Y 付款时间TOTAL_FEE NUMBER(6,2) Y 商品金额TRADE_FEE NUMBER(6,2) Y 订单金额DEAL_FEE NUMBER(6,2) Y 应付金额PAYMENT NUMBER(6,2) Y 实付金额DISCOUNT_FEE NUMBER(6,2) Y 优惠金额POST_FEE NUMBER(4,2) Y 邮费SHIPPING_TYPE VARCHAR2(30) Y 物流方式；卖家包邮,平邮,快递,EMS,虚拟发货BUYER_MEMO VARCHAR2(500) Y 买家备注SELLER_MEMO VARCHAR2(500) Y 卖家备注CREATE_TIME VARCHAR2(50) Y 下单日期MODIFIED_TIME VARCHAR2(50) Y 订单更新日期END_TIME VARCHAR2(50) Y 交易结束日期PAYMAENT_TYPE VARCHAR2(20) Y 买家支付方式BUYER_ALIPAY_NO VARCHAR2(30) Y 买家支付流水号RECEIVER_NAME VARCHAR2(20) Y 收获人姓名RECEIVER_STATE VARCHAR2(30) Y 收货人所在省份RECEIVER_CITY VARCHAR2(30) Y 收货人所在城市RECEIVER_DISTRICT VARCHAR2(100) Y 收货人所在地区RECEIVER_ADDRESS VARCHAR2(200) Y 收货人详细地址RECEIVER_ZIP VARCHAR2(15) Y 收货人邮编RECEIVER_MOBILE VARCHAR2(20) Y 收货人手机RECEIVER_PHONE VARCHAR2(20) Y 收货人电话AVAILABLE_CONFIRM_FEE NUMBER(6,2) Y 交易中剩余的确认收获金额RECEIVED_PAYMENT NUMBER(6,2) Y 卖家实际收到的第三方平台支付金额INVOICE_INFO VARCHAR2(500) Y 发票信息POINT_FEE NUMBER(6) Y 买家实际使用的积分SELLER_RECVRE_FUND NUMBER(6,2) Y 卖家实际收到金额BUYER_RECVRE_FUND NUMBER(6,2) Y 买家收到的退款金额BUYER_OBTAIN_POINT_FEE NUMBER(6) Y 交易成功后买家获得的积分IS_MERGE_ORDER VARCHAR2(51) Y 0正常订单1合并订单2手动合并订单WAREHOUSE VARCHAR2(10) Y 对应仓库AIRLINES VARCHAR2(10) Y 客服URGENT_TASK VARCHAR2(15) Y 加急任务URGENT_TASK_NAME VARCHAR2(20) Y 加急任务名称PROMOTION_NAME VARCHAR2(20) Y 优惠信息的名称PROMOTION_DISCOUNT_FEE NUMBER(6,2) Y 优惠金额（免运费、限时打折时为空）,单位：元GIFT_ITEM_NAME VARCHAR2(20) Y 满就送商品时，所送商品的名称GIFT_ITEM_ID VARCHAR2(20) Y 赠品的宝贝idGIFT_ITEM_NUM NUMBER(2) Y 满就送礼物的礼物数量PROMOTION_DESC VARCHAR2(200) Y 优惠活动的描述PROMOTION_ID VARCHAR2(50) Y 优惠id，(由营销工具id、优惠活动id和优惠详情id组成，结构为：营销工具id-优惠活动id_优惠详情id，如mjs-123024_211143）TO_ERP CHAR(5) Y 0 1：已推送到ERP；0：未推送到ERPBUYER_ALIPAY_ID VARCHAR2(50) Y 买家支付账号
-- Create tablecreate table T_XS_TRADE( TID VARCHAR2(30) not null, SHOP_ID VARCHAR2(30), SHOP_NAME VARCHAR2(50), PT_ID VARCHAR2(10), STATUS_ID VARCHAR2(35), FROM_ID VARCHAR2(30), SHIP_ID VARCHAR2(30), TYPE VARCHAR2(100), BUYER_ID VARCHAR2(20), BUYER_NAME VARCHAR2(50), NUM NUMBER(6), PAYMENT_TIME VARCHAR2(50), TOTAL_FEE NUMBER(6,2), TRADE_FEE NUMBER(6,2), DEAL_FEE NUMBER(6,2), PAYMENT NUMBER(6,2), DISCOUNT_FEE NUMBER(6,2), POST_FEE NUMBER(4,2), SHIPPING_TYPE VARCHAR2(30), BUYER_MEMO VARCHAR2(500), SELLER_MEMO VARCHAR2(500), CREATE_TIME VARCHAR2(50), MODIFIED_TIME VARCHAR2(50), END_TIME VARCHAR2(50), PAYMAENT_TYPE VARCHAR2(20), BUYER_ALIPAY_NO VARCHAR2(30), RECEIVER_NAME VARCHAR2(20), RECEIVER_STATE VARCHAR2(30), RECEIVER_CITY VARCHAR2(30), RECEIVER_DISTRICT VARCHAR2(100), RECEIVER_ADDRESS VARCHAR2(200), RECEIVER_ZIP VARCHAR2(15), RECEIVER_MOBILE VARCHAR2(20), RECEIVER_PHONE VARCHAR2(20), AVAILABLE_CONFIRM_FEE NUMBER(6,2), RECEIVED_PAYMENT NUMBER(6,2), INVOICE_INFO VARCHAR2(500), POINT_FEE NUMBER(6), SELLER_RECVRE_FUND NUMBER(6,2), BUYER_RECVRE_FUND NUMBER(6,2), BUYER_OBTAIN_POINT_FEE NUMBER(6), IS_MERGE_ORDER VARCHAR2(51), WAREHOUSE VARCHAR2(10), AIRLINES VARCHAR2(10), URGENT_TASK VARCHAR2(15), URGENT_TASK_NAME VARCHAR2(20), PROMOTION_NAME VARCHAR2(20), PROMOTION_DISCOUNT_FEE NUMBER(6,2), GIFT_ITEM_NAME VARCHAR2(20), GIFT_ITEM_ID VARCHAR2(20), GIFT_ITEM_NUM NUMBER(2), PROMOTION_DESC VARCHAR2(200), PROMOTION_ID VARCHAR2(50), TO_ERP CHAR(5) default 0, BUYER_ALIPAY_ID VARCHAR2(50))tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited );-- Add comments to the table comment on table T_XS_TRADE is '订单表';-- Add comments to the columns comment on column T_XS_TRADE.TID is '订单编号';comment on column T_XS_TRADE.SHOP_ID is '店铺ID';comment on column T_XS_TRADE.SHOP_NAME is '店铺名称';comment on column T_XS_TRADE.PT_ID is '平台ID';comment on column T_XS_TRADE.STATUS_ID is '订单状态ID';comment on column T_XS_TRADE.FROM_ID is '来源单号';comment on column T_XS_TRADE.SHIP_ID is '物流单号';comment on column T_XS_TRADE.TYPE is '交易类型列表';comment on column T_XS_TRADE.BUYER_ID is '买家账号';comment on column T_XS_TRADE.BUYER_NAME is '买家名称';comment on column T_XS_TRADE.NUM is '商品数量';comment on column T_XS_TRADE.PAYMENT_TIME is '付款时间';comment on column T_XS_TRADE.TOTAL_FEE is '商品金额';comment on column T_XS_TRADE.TRADE_FEE is '订单金额';comment on column T_XS_TRADE.DEAL_FEE is '应付金额';comment on column T_XS_TRADE.PAYMENT is '实付金额';comment on column T_XS_TRADE.DISCOUNT_FEE is '优惠金额';comment on column T_XS_TRADE.POST_FEE is '邮费';comment on column T_XS_TRADE.SHIPPING_TYPE is '物流方式；卖家包邮,平邮,快递,EMS,虚拟发货';comment on column T_XS_TRADE.BUYER_MEMO is '买家备注';comment on column T_XS_TRADE.SELLER_MEMO is '卖家备注';comment on column T_XS_TRADE.CREATE_TIME is '下单日期';comment on column T_XS_TRADE.MODIFIED_TIME is '订单更新日期';comment on column T_XS_TRADE.END_TIME is '交易结束日期';comment on column T_XS_TRADE.PAYMAENT_TYPE is '买家支付方式';comment on column T_XS_TRADE.BUYER_ALIPAY_NO is '买家支付流水号';comment on column T_XS_TRADE.RECEIVER_NAME is '收获人姓名';comment on column T_XS_TRADE.RECEIVER_STATE is '收货人所在省份';comment on column T_XS_TRADE.RECEIVER_CITY is '收货人所在城市';comment on column T_XS_TRADE.RECEIVER_DISTRICT is '收货人所在地区';comment on column T_XS_TRADE.RECEIVER_ADDRESS is '收货人详细地址';comment on column T_XS_TRADE.RECEIVER_ZIP is '收货人邮编';comment on column T_XS_TRADE.RECEIVER_MOBILE is '收货人手机';comment on column T_XS_TRADE.RECEIVER_PHONE is '收货人电话';comment on column T_XS_TRADE.AVAILABLE_CONFIRM_FEE is '交易中剩余的确认收获金额';comment on column T_XS_TRADE.RECEIVED_PAYMENT is '卖家实际收到的第三方平台支付金额';comment on column T_XS_TRADE.INVOICE_INFO is '发票信息';comment on column T_XS_TRADE.POINT_FEE is '买家实际使用的积分';comment on column T_XS_TRADE.SELLER_RECVRE_FUND is '卖家实际收到金额';comment on column T_XS_TRADE.BUYER_RECVRE_FUND is '买家收到的退款金额';comment on column T_XS_TRADE.BUYER_OBTAIN_POINT_FEE is '交易成功后买家获得的积分';comment on column T_XS_TRADE.IS_MERGE_ORDER is '0正常订单1合并订单2手动合并订单';comment on column T_XS_TRADE.WAREHOUSE is '对应仓库';comment on column T_XS_TRADE.AIRLINES is '客服';comment on column T_XS_TRADE.URGENT_TASK is '加急任务';comment on column T_XS_TRADE.URGENT_TASK_NAME is '加急任务名称';comment on column T_XS_TRADE.PROMOTION_NAME is '优惠信息的名称';comment on column T_XS_TRADE.PROMOTION_DISCOUNT_FEE is '优惠金额（免运费、限时打折时为空）,单位：元';comment on column T_XS_TRADE.GIFT_ITEM_NAME is '满就送商品时，所送商品的名称';comment on column T_XS_TRADE.GIFT_ITEM_ID is '赠品的宝贝id';comment on column T_XS_TRADE.GIFT_ITEM_NUM is '满就送礼物的礼物数量';comment on column T_XS_TRADE.PROMOTION_DESC is '优惠活动的描述';comment on column T_XS_TRADE.PROMOTION_ID is '优惠id，(由营销工具id、优惠活动id和优惠详情id组成，结构为：营销工具id-优惠活动id_优惠详情id，如mjs-123024_211143）';comment on column T_XS_TRADE.TO_ERP is '1：已推送到ERP；0：未推送到ERP';comment on column T_XS_TRADE.BUYER_ALIPAY_ID is '买家支付账号';-- Create/Recreate primary, unique and foreign key constraints alter table T_XS_TRADE add constraint 订单编号 primary key (TID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );-- Create/Recreate indexes create index 平台名称 on T_XS_TRADE (PT_ID) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );create index 店铺名称 on T_XS_TRADE (SHOP_ID) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );create index 收货人所在城市 on T_XS_TRADE (RECEIVER_CITY) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );create index 收货人所在省份 on T_XS_TRADE (RECEIVER_STATE) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );create index 物流方式 on T_XS_TRADE (SHIPPING_TYPE) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );create index 订单状态 on T_XS_TRADE (STATUS_ID) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );create index 订单金额 on T_XS_TRADE (TRADE_FEE) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );