请教下各位
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`id` int(11) NOT NULL auto_increment COMMENT '主键ID',
`c_id` int(11) NOT NULL COMMENT 'CID',
`name` varchar(128) NOT NULL COMMENT '市场名',
`status` int(11) NOT NULL COMMENT '统计类型,1-A类,2-B类',
`oper_status` int(11) default NULL COMMENT '1-打开;2-关闭;3-无效',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES ('1', '17', 'name', '1', '1');
INSERT INTO `result` VALUES ('2', '18', 'name', '1', '1');
INSERT INTO `result` VALUES ('3', '18', 'name', '2', '1');
INSERT INTO `result` VALUES ('4', '19', 'name', '1', '1');
INSERT INTO `result` VALUES ('5', '19', 'name', '2', '2');
INSERT INTO `result` VALUES ('6', '19', 'name', '2', '3');
INSERT INTO `result` VALUES ('6', '20', 'name', '2', '3');希望得到的表结果
根据c_id分组统计status为1或2的数量,且oper_status=1,且c_id来自一个LIST(长度不固定)
c_id count(status=1) count(status=2)
17 1 0
18 1 1
19 1 0
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`id` int(11) NOT NULL auto_increment COMMENT '主键ID',
`c_id` int(11) NOT NULL COMMENT 'CID',
`name` varchar(128) NOT NULL COMMENT '市场名',
`status` int(11) NOT NULL COMMENT '统计类型,1-A类,2-B类',
`oper_status` int(11) default NULL COMMENT '1-打开;2-关闭;3-无效',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES ('1', '17', 'name', '1', '1');
INSERT INTO `result` VALUES ('2', '18', 'name', '1', '1');
INSERT INTO `result` VALUES ('3', '18', 'name', '2', '1');
INSERT INTO `result` VALUES ('4', '19', 'name', '1', '1');
INSERT INTO `result` VALUES ('5', '19', 'name', '2', '2');
INSERT INTO `result` VALUES ('6', '19', 'name', '2', '3');
INSERT INTO `result` VALUES ('6', '20', 'name', '2', '3');希望得到的表结果
根据c_id分组统计status为1或2的数量,且oper_status=1,且c_id来自一个LIST(长度不固定)
c_id count(status=1) count(status=2)
17 1 0
18 1 1
19 1 0
c_id,
sum(if(status=1,1,0)) as count(if(status=2,1,0)),
sum(status=2) as count(status=2)
from result group by c_id
SELECT
c_id,
sum(if(status=1,1,0)) as count(status=1),
sum(if(status=2,1,0)) as count(status=2)
from result group by c_id
不好意思!
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...