56,677
社区成员
发帖
与我相关
我的任务
分享
select t1.*,
CONCAT(CASE WHEN t2.totalTransaction = 0 OR t2.totalTransaction IS NULL
THEN 0
ELSE ((t1.totalTransaction-t2.totalTransaction) / t2.totalTransaction) * 100 END , '%') AS totalTransactionProportionT,
CONCAT(CASE WHEN t3.totalTransaction = 0 OR t3.totalTransaction IS NULL
THEN 0
ELSE ((t1.totalTransaction-t3.totalTransaction) / t3.totalTransaction) * 100 END , '%') AS totalTransactionProportionH,
CONCAT(CASE WHEN t2.totalVolumes = 0 OR t2.totalVolumes IS NULL
THEN 0
ELSE ((t1.totalVolumes-t2.totalVolumes) / t2.totalVolumes) * 100 END , '%') AS totalVolumesProportionT,
CONCAT(CASE WHEN t3.totalVolumes = 0 OR t3.totalVolumes IS NULL
THEN 0
ELSE ((t1.totalVolumes-t3.totalVolumes) / t3.totalVolumes) * 100 END , '%') AS totalVolumesProportionH,
CONCAT(CASE WHEN t2.totalTransactionMoney = 0 OR t2.totalTransactionMoney IS NULL
THEN 0
ELSE ((t1.totalTransactionMoney-t2.totalTransactionMoney) / t2.totalTransactionMoney) * 100 END , '%') AS totalTransactionMoneyProportionT,
CONCAT(CASE WHEN t3.totalTransactionMoney = 0 OR t3.totalTransactionMoney IS NULL
THEN 0
ELSE ((t1.totalTransactionMoney-t3.totalTransactionMoney) / t3.totalTransactionMoney) * 100 END , '%') AS totalTransactionMoneyProportionH,
CONCAT(CASE WHEN t2.totalVolumesMoney = 0 OR t2.totalVolumesMoney IS NULL
THEN 0
ELSE ((t1.totalVolumesMoney-t2.totalVolumesMoney) / t2.totalVolumesMoney) * 100 END , '%') AS totalVolumesMoneyProportionT,
CONCAT(CASE WHEN t3.totalVolumesMoney = 0 OR t3.totalVolumesMoney IS NULL
THEN 0
ELSE ((t1.totalVolumesMoney-t3.totalVolumesMoney) / t3.totalVolumesMoney) * 100 END , '%') AS totalVolumesMoneyProportionH
from
(select
DATE_FORMAT(price.end_time,'%Y-%m') as yearMonth,
sum(item.TRADING_WEIGHT) as totalTransaction,
sum(case when price.state = "6" then item.TRADING_WEIGHT else 0 end) as totalVolumes,
sum(case when batch.OFFER_TYPE = "2" then price.STARTING_PIRCE else (price.STARTING_PIRCE * item.TRADING_WEIGHT) end) as totalTransactionMoney,
sum(case when price.state = "6" then
case batch.OFFER_TYPE when "2" then price.STARTING_PIRCE
when "1" then (price.STARTING_PIRCE * item.TRADING_WEIGHT) end
else 0 end) as totalVolumesMoney,
sum(case when price.state = "6" then
case batch.OFFER_TYPE when "2" then (price.CURRENT_PRICE - price.STARTING_PIRCE)
when "1" then (item.TRADING_WEIGHT * (price.CURRENT_PRICE - price.STARTING_PIRCE)) end
else 0 end) as increasePrice,
sum(case when price.state = "7" then item.TRADING_WEIGHT else 0 end) as totalAbortive
from bdt_resources_item item,bdt_price_info price,bdt_batch batch
where
batch.id = price.batch_id and
item.id = price.item_id and
price.state in ("6","7") and
price.end_time >='2017-04-01' and
price.end_time <='2017-07-01') as t1,
(select
sum(item2.TRADING_WEIGHT) as totalTransaction,
sum(case when price2.state = "6" then item2.TRADING_WEIGHT else 0 end) as totalVolumes,
sum(case when batch2.OFFER_TYPE = "2" then price2.STARTING_PIRCE else (price2.STARTING_PIRCE * item2.TRADING_WEIGHT) end) as totalTransactionMoney,
sum(case when price2.state = "6" then
case batch2.OFFER_TYPE when "2" then price2.STARTING_PIRCE
when "1" then (price2.STARTING_PIRCE * item2.TRADING_WEIGHT) end
else 0 end) as totalVolumesMoney
from bdt_resources_item item2,bdt_price_info price2,bdt_batch batch2
where
batch2.id = price2.batch_id and
item2.id = price2.item_id and
price2.state in ("6","7") and
price2.end_time >=DATE_SUB('2017-04-01',INTERVAL 1 YEAR) and
price2.end_time <= DATE_SUB('2017-07-01',INTERVAL 1 YEAR)) as t2,
(select
sum(item3.TRADING_WEIGHT) as totalTransaction,
sum(case when price3.state = "6" then item3.TRADING_WEIGHT else 0 end) as totalVolumes,
sum(case when batch3.OFFER_TYPE = "2" then price3.STARTING_PIRCE else (price3.STARTING_PIRCE * item3.TRADING_WEIGHT) end) as totalTransactionMoney,
sum(case when price3.state = "6" then
case batch3.OFFER_TYPE when "2" then price3.STARTING_PIRCE
when "1" then (price3.STARTING_PIRCE * item3.TRADING_WEIGHT) end
else 0 end) as totalVolumesMoney
from bdt_resources_item item3,bdt_price_info price3,bdt_batch batch3
where
batch3.id = price3.batch_id and
item3.id = price3.item_id and
price3.state in ("6","7") and
price3.end_time >=DATE_SUB('2017-04-01',INTERVAL 1 MONTH) and
price3.end_time <= DATE_SUB('2017-07-01',INTERVAL 1 MONTH)) as t3
+-----------+------------------+--------------+-----------------------+-------------------+---------------+---------------+-----------------------------+-----------------------------+-------------------------+-------------------------+----------------------------------+----------------------------------+------------------------------+------------------------------+
| yearMonth | totalTransaction | totalVolumes | totalTransactionMoney | totalVolumesMoney | increasePrice | totalAbortive | totalTransactionProportionT | totalTransactionProportionH | totalVolumesProportionT | totalVolumesProportionH | totalTransactionMoneyProportionT | totalTransactionMoneyProportionH | totalVolumesMoneyProportionT | totalVolumesMoneyProportionH |
+-----------+------------------+--------------+-----------------------+-------------------+---------------+---------------+-----------------------------+-----------------------------+-------------------------+-------------------------+----------------------------------+----------------------------------+------------------------------+------------------------------+
| 2017-06 | 11324.0000 | 5716.0000 | 14768439.000000 | 7376661.000000 | 718291.000000 | 5608.0000 | 0% | 4.25336034% | 0% | 4.21148587% | 0% | 0.0016657420% | 0% | 0.0016674489% |
+-----------+------------------+--------------+-----------------------+-------------------+---------------+---------------+-----------------------------+-----------------------------+-------------------------+-------------------------+----------------------------------+----------------------------------+------------------------------+------------------------------+
1 row in set
想要的结果类似于是这样的 把时间段内的月份都展示出来,如果没有则为0,按月统计而不是统计时间段内的所有金额
+-----------+------------------+--------------+-----------------------+-------------------+---------------+---------------+-----------------------------+-----------------------------+-------------------------+-------------------------+----------------------------------+----------------------------------+------------------------------+------------------------------+
| yearMonth | totalTransaction | totalVolumes | totalTransactionMoney | totalVolumesMoney | increasePrice | totalAbortive | totalTransactionProportionT | totalTransactionProportionH | totalVolumesProportionT | totalVolumesProportionH | totalTransactionMoneyProportionT | totalTransactionMoneyProportionH | totalVolumesMoneyProportionT | totalVolumesMoneyProportionH |
+-----------+------------------+--------------+-----------------------+-------------------+---------------+---------------+-----------------------------+-----------------------------+-------------------------+-------------------------+----------------------------------+----------------------------------+------------------------------+------------------------------+
| 2017-05 | 6369.0000 | 3123.0000 | 1457955.000000 | 721413.000000 | 718290.000000 | 3246.0000 | 41.75383931% | 0.00000000% | 32.21845893% | 0.00000000% | -89.0463641597% | 0.0000000000% | -89.1600383163% | 0.0000000000% |
| 2017-06 | 462.0000 | 231.0000 | 246.000000 | 123.000000 | 1.000000 | 231.0000 | -89.71733808% | -92.74611399% | -90.22015241% | -92.60326609% | -99.9981517986% | -99.9831270512% | -99.9981518003% | -99.9829501270% |
+-----------+------------------+--------------+-----------------------+-------------------+---------------+---------------+-----------------------------+-----------------------------+-------------------------+-------------------------+----------------------------------+----------------------------------+------------------------------+------------------------------+
2 rows in set
-- ----------------------------
-- Table structure for `bdt_batch`
-- ----------------------------
DROP TABLE IF EXISTS `bdt_batch`;
CREATE TABLE `bdt_batch` (
`ID` varchar(30) NOT NULL COMMENT '主键(PM200600001)',
`BATCH_NAME` varchar(100) DEFAULT NULL COMMENT '场次名称',
`MEMBER_NAME` varchar(30) DEFAULT NULL COMMENT '会员名称',
`MEMBER_CODE` varchar(30) NOT NULL COMMENT '发起竞价的会员代码',
`BATCH_STATE` varchar(2) NOT NULL COMMENT '委托状态:0-已生成,1-已发布,2.竞价中 3-已撤消,4--已作废,5-已完成',
`BATCH_DATE` datetime NOT NULL COMMENT '委托申请时间',
`END_TIME` datetime NOT NULL COMMENT '结束时间',
`START_TIME` datetime NOT NULL COMMENT '开始时间',
`BOND_PAYMENT_STATUS` varchar(2) DEFAULT NULL COMMENT '履约保证金是否已经支付 1 - 已经支付 0 - 未支付(只有此状态为1才允许开始拍卖)',
`SETTLEMENT_MODE` varchar(2) DEFAULT NULL COMMENT '结算方式:1-场外结算(默认) 2- 场内结算',
`INVALID_DATE` datetime DEFAULT NULL COMMENT '作废时间',
`AUDIT_OPERATOR` varchar(30) DEFAULT NULL COMMENT '审核操作员',
`STOP_OPERATOR` varchar(30) DEFAULT NULL COMMENT '终止操作员',
`INVALID_OPERATOR` varchar(30) DEFAULT NULL COMMENT '作废操作员',
`APPLY_OPERATOR` varchar(30) DEFAULT NULL COMMENT '申请操作员',
`ANNOUNCEMENT_DAY` datetime DEFAULT NULL COMMENT '公告日',
`BID_DAY` datetime DEFAULT NULL COMMENT '竞价日',
`BUYER_DEPOSIT` decimal(20,2) DEFAULT NULL COMMENT '买方保证金',
`SELLER_BOND` decimal(20,2) DEFAULT NULL COMMENT '卖家保证金',
`FORCED_TERMINATION_REASON` varchar(200) DEFAULT NULL COMMENT '强制终止理由',
`AUDIT_STOP_REASON` varchar(200) DEFAULT NULL COMMENT '终止委托审核理由(待定)',
`AUDIT_STOP_DATE` datetime DEFAULT NULL COMMENT '终止委托审核时间(待定)',
`APPLY_STOP_REASON` varchar(200) DEFAULT NULL COMMENT '终止委托申请理由(待定)',
`APPLY_STOP_DATE` datetime DEFAULT NULL COMMENT '终止委托申请时间(待定)',
`REFUSE_AUDIT_REASON` varchar(200) DEFAULT NULL COMMENT '拒绝审核委托理由(待定)',
`AUDIT_TIME` datetime DEFAULT NULL COMMENT '委托审核时间(待定)',
`IS_DIRECTIONAL_BID` varchar(2) DEFAULT NULL COMMENT '定向竞价 0定向 1非定向',
`DIRECTIONAL_REASON` varchar(200) DEFAULT NULL COMMENT '定向的原因,当场次定向时必须输入',
`IS_SEND_MESSAGE` varchar(2) DEFAULT NULL COMMENT '是否需要发送短信0不发送1发送',
`IS_SHOW_DEAL_PRICE` varchar(2) DEFAULT NULL COMMENT '是否显示中标价格',
`IS_SHOW_ORDER_PRICE` varchar(2) DEFAULT NULL COMMENT '是否显示定价',
`IS_SHOW_DEAL_MEMBER` varchar(2) DEFAULT NULL COMMENT '是否显示中标会员',
`MINIMUM_RESPONSE` varchar(30) DEFAULT NULL COMMENT '最少响应人数',
`ISSHOWDEALRESULT` varchar(2) DEFAULT NULL COMMENT '是否显示成交结果',
`BIDDING_TYPE` varchar(2) NOT NULL DEFAULT '1' COMMENT '竞价模式:1-公开增价,2自由报价,3-荷式竞价',
`OFFER_TYPE` varchar(2) NOT NULL DEFAULT '2' COMMENT '报盘方式:1-单价,2-总价',
`EDITMENT` longtext COMMENT '协议',
`IS_SHOW_STARTING_PIRCE` varchar(2) DEFAULT NULL COMMENT '是否显示起拍价 1显示(默认) 2不显示 ',
`DOAMIN` varchar(10) DEFAULT 'HGB' COMMENT '域名',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of bdt_batch
-- ----------------------------
INSERT INTO `bdt_batch` VALUES ('P170508004', '32', 'member2', 'U1234', '0', '2017-05-08 13:53:35', '2017-05-08 15:55:00', '2017-05-08 13:55:00', null, '1', null, null, null, null, null, '2017-05-08 00:00:00', '2017-05-08 00:00:00', '12.00', null, null, null, null, null, null, null, null, '1', '', null, null, null, null, '2', null, '1', '2', '', '1', 'HGB');
INSERT INTO `bdt_batch` VALUES ('P170508005', '32', 'member1', 'U1234', '0', '2017-05-08 14:38:12', '2017-05-08 15:55:00', '2017-05-08 13:55:00', null, '1', null, null, null, null, null, '2017-05-08 00:00:00', '2017-05-08 00:00:00', '12.00', null, null, null, null, null, null, null, null, '1', '', null, null, null, null, '2', null, '1', '2', '', '1', 'HGB');
-- ----------------------------
-- Table structure for `bdt_price_info`
-- ----------------------------
DROP TABLE IF EXISTS `bdt_price_info`;
CREATE TABLE `bdt_price_info` (
`ID` varchar(30) NOT NULL COMMENT 'ID',
`BATCH_ID` varchar(30) DEFAULT NULL COMMENT '场次ID',
`ITEM_ID` int(11) DEFAULT NULL COMMENT '物资号',
`BUYER_DEPOSIT` decimal(20,2) DEFAULT NULL COMMENT '卖家保证金',
`BIDDING_GRADIENT` decimal(20,2) DEFAULT NULL COMMENT '报价梯度/降价梯度',
`STARTING_PIRCE` decimal(20,2) NOT NULL COMMENT '起拍价(若为总价报盘,则为总价,否则为单价)',
`MIN_PIRCE` decimal(20,2) DEFAULT NULL COMMENT '销售底价(若为总价报盘,则为总价,否则为单价)',
`SHOW_PIRCE` varchar(2) DEFAULT '1' COMMENT '是否显示底价:1-显示,2-不显示',
`STATE` varchar(2) DEFAULT '0' COMMENT '竞价状态:0-已生成,1-已发布,2.竞价中 3-已撤消,4--已作废 5已结束6已成交7已流标',
`START_TIME` datetime DEFAULT NULL COMMENT '开始时间',
`END_TIME` datetime DEFAULT NULL COMMENT '结束时间',
`WINNING_USER` varchar(30) DEFAULT NULL COMMENT '中标会员',
`WINNING_CONTRACT` varchar(30) DEFAULT NULL COMMENT '中标合同号',
`WINNING_DATE` datetime DEFAULT NULL COMMENT '中标时间',
`TOTAL_PRICE` decimal(20,2) DEFAULT NULL COMMENT '出价金额(总价)',
`REMARK` varchar(200) DEFAULT NULL COMMENT '备注:流标因为',
`TOTAL_NUMBER` decimal(18,4) DEFAULT NULL COMMENT '中标数量',
`FLOW_INFO` varchar(2) NOT NULL DEFAULT '0' COMMENT '流标原因:0未流标1响应人数不足2无人出价3未到保留价',
`CURRENT_PRICE` decimal(16,2) DEFAULT NULL COMMENT '当前价',
`UNIT_PRICE` decimal(18,2) DEFAULT NULL COMMENT '拼盘单价',
`IS_SHOW_CURPRICE` varchar(2) DEFAULT NULL COMMENT '是否启用显示最新价格(1显示0不显示)',
`BUY_RATE` decimal(18,8) DEFAULT NULL COMMENT '买家服务费率',
`SELL_RATE` decimal(18,8) DEFAULT NULL COMMENT '卖家服务费率',
`SETTLEMENT_UNIT` varchar(30) DEFAULT NULL COMMENT '结算公司',
`MIN_WEIGHT` decimal(18,4) DEFAULT NULL COMMENT '最小起拍量(荷式拍卖)',
`DOWN_PRICE_INTERVAL` int(11) DEFAULT NULL COMMENT '降价间隔时间(荷式拍卖)',
`IS_SHOW_TRANSACTION_PRICE` varchar(2) DEFAULT NULL COMMENT '是否显示成交价',
`RESERVE_PRICE` decimal(18,2) DEFAULT NULL COMMENT '保留价',
`TAX_RATE` decimal(10,4) DEFAULT NULL COMMENT '税率',
`TAX_FREE_PRICE` decimal(10,4) DEFAULT NULL COMMENT '不含税价',
`DOAMIN` varchar(10) DEFAULT 'HGB' COMMENT '域名',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of bdt_price_info
-- ----------------------------
INSERT INTO `bdt_price_info` VALUES ('P170508004001', 'P170508004', '23', null, '61.50', '123.00', null, '1', '6', '2017-05-08 13:55:00', '2017-06-08 15:55:00', 'U34663', null, null, null, null, null, '0', '124.00', null, null, null, null, null, null, null, null, '123.00', '13.0000', '107.0100', 'HGB');
INSERT INTO `bdt_price_info` VALUES ('P170508005001', 'P170508005', '24', null, '61.50', '123.00', null, '1', '7', '2017-05-08 13:55:00', '2017-06-21 15:57:00', 'U34663', null, null, null, null, null, '0', '124.00', null, null, null, null, null, null, null, null, '123.00', '13.0000', '107.0100', 'HGB');
INSERT INTO `bdt_price_info` VALUES ('P170508005002', 'P170508005', '25', null, '12.00', '12.00', null, '1', '6', '2017-05-08 13:55:00', '2016-05-08 15:55:00', 'U34663', null, null, null, null, null, '0', null, null, null, null, null, null, null, null, null, '123.00', '123.0000', '107.0100', 'HGB');
-- ----------------------------
-- Table structure for `bdt_resources_item`
-- ----------------------------
DROP TABLE IF EXISTS `bdt_resources_item`;
CREATE TABLE `bdt_resources_item` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '物资号',
`PRICE_INFO_ID` varchar(30) DEFAULT NULL COMMENT '拼盘号',
`BATCH_ID` varchar(30) DEFAULT NULL COMMENT '场次ID',
`PACKAGES` varchar(30) DEFAULT NULL COMMENT '材质(包装)',
`PRODUCT_NAME` varchar(30) DEFAULT NULL COMMENT '品名',
`PRODUCT_CODE` varchar(30) DEFAULT NULL COMMENT '品名代码',
`RATE` decimal(10,4) DEFAULT NULL COMMENT '品名费率',
`SPECIFICATION` varchar(30) DEFAULT NULL COMMENT '规格(质量标准)',
`ORIGIN_PLACE` varchar(30) DEFAULT NULL COMMENT '产地',
`PIECE_UNIT` varchar(10) DEFAULT NULL COMMENT '计量单位',
`TRADING_QUANTITY` decimal(18,4) DEFAULT NULL COMMENT '交易数量',
`TRADING_WEIGHT` decimal(18,4) DEFAULT NULL COMMENT '交易重量',
`WAREHOUSE_NAME` varchar(30) DEFAULT NULL COMMENT '仓库',
`REMARKS` varchar(100) DEFAULT NULL COMMENT '备注',
`PAYMENT_METHOD` varchar(30) DEFAULT NULL COMMENT '付款方式',
`DELIVERY_METHOD` varchar(30) DEFAULT NULL COMMENT '提货方式',
`DELIVERY_DATE` varchar(30) DEFAULT NULL COMMENT '交货时间',
`ATTACH_ID` int(30) DEFAULT NULL COMMENT '附件ID',
`WAREHOUSE_LOCATION` varchar(30) DEFAULT NULL COMMENT '仓库所在地',
`PRODUCT_NO` varchar(30) DEFAULT NULL COMMENT '产品代码',
`DOAMIN` varchar(10) DEFAULT 'HGB' COMMENT '域名',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of bdt_resources_item
-- ----------------------------
INSERT INTO `bdt_resources_item` VALUES ('23', 'P170508004001', 'P170508004', '23', '品名6', 'code6', null, '123', '321', '吨', null, '231.0000', '321', '312', '现汇', '自提', null, null, null, null, 'HGB');
INSERT INTO `bdt_resources_item` VALUES ('24', 'P170508005001', 'P170508005', '23', '品名6', 'code6', null, '123', '321', '吨', null, '231.0000', '321', '312', '现汇', '自提', null, null, null, null, 'HGB');
INSERT INTO `bdt_resources_item` VALUES ('25', 'P170508005002', 'P170508005', '22', '品名6', 'code6', null, '123', '213', '吨', null, '231.0000', '213', '123', null, null, null, null, null, null, 'HGB');