Oracle 分类统计sql
--按店铺分类统计订单状态
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 店铺ID SHOP_NAME VARCHAR2(50) Y 店铺名称 PT_ID VARCHAR2(10) Y 平台ID STATUS_ID VARCHAR2(35) Y 订单状态ID FROM_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 赠品的宝贝id GIFT_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：未推送到ERP BUYER_ALIPAY_ID VARCHAR2(50) Y 买家支付账号
表结构sql语句形式
-- Create table create 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 );
