Oracle 分类统计sql

2023-06-15 08:58•Android•阅读 4479

--按店铺分类统计订单状态
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
  );

上一篇 »python统计自己微信好友并抓取信息
下一篇 »用SQL命令查看Mysql数据库大小统计数据库空间占用

Oracle 分类统计sql

相关推荐

Oracle学习，四_SQL函数

Oracle固定SQL的执行计划，一---SQL Profile

Oracle性能-查询统计信息的SQL语句

统计mysql数据库中数据表/字段等数量的sql

mysql 统计sql

统计mysql数据库中数据表/字段等数量的sql - 幕码人

Oracle学习笔记_01_SQL初步

.Net程序员学用Oracle系列，28：PLSQL 之SQL分类和动态SQL