资源描述:
《oracle分类统计sql》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、Oracle分类统计sql--按店铺分类统计订单状态select sum(A)as 已付款 ,sum(B)as 未付款,Cas 店铺名from( select case when status_id=2then 1else 0end AS A, case when status_id=4then 1else 0end AS B, shop_idAS C from t_xs_tradewhere status_idin(2,4))group by C; 结果:
2、已付款未付款店铺名1625shop042173229shop02311shop084270shop01530shop06610338855006870 表结构:TIDVARCHAR2(30)N订单编号SHOP_IDVARCHAR2(30)Y店铺IDSHOP_NAMEVARCHAR2(50)Y店铺名称PT_IDVARCHAR2(10)Y平台IDSTATUS_IDVARCHAR2(35)Y订单状态IDFROM_IDVARCHAR2(30)Y来源单号SHIP_IDVARCHAR2(30)Y物
3、流单号TYPEVARCHAR2(100)Y交易类型列表BUYER_IDVARCHAR2(20)Y买家账号BUYER_NAMEVARCHAR2(50)Y买家名称NUMNUMBER(6)Y商品数量PAYMENT_TIMEVARCHAR2(50)Y付款时间TOTAL_FEENUMBER(6,2)Y商品金额TRADE_FEENUMBER(6,2)Y订单金额DEAL_FEENUMBER(6,2)Y应付金额PAYMENTNUMBER(6,2)Y实付金额DISCOUNT_FEENUMBER(6,2)Y优惠金额P
4、OST_FEENUMBER(4,2)Y邮费SHIPPING_TYPEVARCHAR2(30)Y物流方式;卖家包邮,平邮,快递,EMS,虚拟发货BUYER_MEMOVARCHAR2(500)Y买家备注SELLER_MEMOVARCHAR2(500)Y卖家备注CREATE_TIMEVARCHAR2(50)Y下单日期MODIFIED_TIMEVARCHAR2(50)Y订单更新日期END_TIMEVARCHAR2(50)Y交易结束日期PAYMAENT_TYPEVARCHAR2(20)Y买家支付方式BUYE
5、R_ALIPAY_NOVARCHAR2(30)Y买家支付流水号RECEIVER_NAMEVARCHAR2(20)Y收获人姓名RECEIVER_STATEVARCHAR2(30)Y收货人所在省份RECEIVER_CITYVARCHAR2(30)Y收货人所在城市RECEIVER_DISTRICTVARCHAR2(100)Y收货人所在地区RECEIVER_ADDRESSVARCHAR2(200)Y收货人详细地址RECEIVER_ZIPVARCHAR2(15)Y收货人邮编RECEIVER_MOBILEVA
6、RCHAR2(20)Y收货人手机RECEIVER_PHONEVARCHAR2(20)Y收货人电话AVAILABLE_CONFIRM_FEENUMBER(6,2)Y交易中剩余的确认收获金额RECEIVED_PAYMENTNUMBER(6,2)Y卖家实际收到的第三方平台支付金额INVOICE_INFOVARCHAR2(500)Y发票信息POINT_FEENUMBER(6)Y买家实际使用的积分SELLER_RECVRE_FUNDNUMBER(6,2)Y卖家实际收到金额BUYER_RECVRE_FUNDN
7、UMBER(6,2)Y买家收到的退款金额BUYER_OBTAIN_POINT_FEENUMBER(6)Y交易成功后买家获得的积分IS_MERGE_ORDERVARCHAR2(51)Y0正常订单1合并订单2手动合并订单WAREHOUSEVARCHAR2(10)Y对应仓库AIRLINESVARCHAR2(10)Y客服URGENT_TASKVARCHAR2(15)Y加急任务URGENT_TASK_NAMEVARCHAR2(20)Y加急任务名称PROMOTION_NAMEVARCHAR2(20)Y优惠信息
8、的名称PROMOTION_DISCOUNT_FEENUMBER(6,2)Y优惠金额(免运费、限时打折时为空),单位:元GIFT_ITEM_NAMEVARCHAR2(20)Y满就送商品时,所送商品的名称GIFT_ITEM_IDVARCHAR2(20)Y赠品的宝贝idGIFT_ITEM_NUMNUMBER(2)Y满就送礼物的礼物数量PROMOTION_DESCVARCHAR2(200)Y优惠活动的描述PROMOTION_IDVARCHAR2(50)Y优惠id,(由营销工具id、优惠活动