mysql 在一个表中,要把同一个字段,不同值几个记录的另一个字段的值 一次取出来(也就是用一条sql),这个表的数据量比较大,请教下有什么好办法?
表结构:
CREATE TABLE `statics` (
`BussinessID` int(10) unsigned NOT NULL COMMENT '业务ID',
`StatisticsDate` int(10) unsigned NOT NULL COMMENT '统计日期,精确到天',
`ProvCode` tinyint(3) unsigned NOT NULL COMMENT '所属省份编码',
`SendType` tinyint(3) unsigned NOT NULL COMMENT '发送类型: 0:类型0 1:类型1 2:类型2 3:类型3',
`DeliverSuccQty` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '成功量',
`DeliverFailQty` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '失败量',
PRIMARY KEY (`StatisticsDate`,`BussinessID`,`ProvCode`,`SendType`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='统计表';
问题:统计不同发送类型的成功量和失败量,用一条SQL一次取出来? sqlmysqltable
表结构:
CREATE TABLE `statics` (
`BussinessID` int(10) unsigned NOT NULL COMMENT '业务ID',
`StatisticsDate` int(10) unsigned NOT NULL COMMENT '统计日期,精确到天',
`ProvCode` tinyint(3) unsigned NOT NULL COMMENT '所属省份编码',
`SendType` tinyint(3) unsigned NOT NULL COMMENT '发送类型: 0:类型0 1:类型1 2:类型2 3:类型3',
`DeliverSuccQty` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '成功量',
`DeliverFailQty` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '失败量',
PRIMARY KEY (`StatisticsDate`,`BussinessID`,`ProvCode`,`SendType`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='统计表';
问题:统计不同发送类型的成功量和失败量,用一条SQL一次取出来? sqlmysqltable
from statics
group by SendType
sum(if(SendType=1,DeliverFailQty,0))
from statics
sum(if(SendType=0,DeliverFailQty,0)),
sum(if(SendType=1,DeliverSuccQty,0)),
sum(if(SendType=1,DeliverFailQty,0)),
sum(if(SendType=2,DeliverSuccQty,0)),
sum(if(SendType=2,DeliverFailQty,0)),
sum(if(SendType=3,DeliverSuccQty,0)),
sum(if(SendType=3,DeliverFailQty,0))
from statics
以下方法也有同工之妙 select
StatisticsDate,
sum(case SendType when 0 then DeliverSuccQty+DeliverFailQty end) count0,
sum(case SendType when 1 then DeliverSuccQty+DeliverFailQty end) count1,
sum(case SendType when 2 then DeliverSuccQty+DeliverFailQty end) count2,
sum(case SendType when 3 then DeliverSuccQty+DeliverFailQty end) count3
from statics
group by StatisticsDate
order by StatisticsDate