mysql 自定义 function()

创建表，添加数据

CREATE TABLE `function` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `state` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of function
-- ----------------------------
INSERT INTO `function` VALUES ('1', 'ht01', 'qq');
INSERT INTO `function` VALUES ('2', 'ht01', 'rr');
INSERT INTO `function` VALUES ('3', 'ht01', 'bb');
INSERT INTO `function` VALUES ('4', 'ht02', 'qq');
INSERT INTO `function` VALUES ('5', 'ht02', 'yy');
INSERT INTO `function` VALUES ('6', 'ht03', 'gg');
INSERT INTO `function` VALUES ('7', 'ht03', 'bb');
INSERT INTO `function` VALUES ('8', 'ht01', 'qq');
INSERT INTO `function` VALUES ('9', 'ht01', 'qq');

自定义方法

CREATE FUNCTION countFunction(
	in_name VARCHAR(255),
	num  INT
)
RETURNS varchar(255)
BEGIN
DECLARE cont int;
DECLARE result VARCHAR(255);
  SELECT COUNT(*) into cont FROM `function` f where f.state = "qq" and f.name= in_name ;
	 set result = CONCAT(cont,"/",num);
RETURN(result);
END

自定义方法调用

SELECT name ,countFunction(`name`,COUNT(*)) result from `function` GROUP BY name

执行结果

name    result

ht01 3/5
ht02 1/2
ht03 0/2