mysql语句查询

Question

DROP TABLE IF EXISTS merchants;
CREATE TABLE merchants (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
ip char(15) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of merchants
-- ----------------------------
INSERT INTO merchants VALUES ('1', 'abc.com', '127.0.0.1');
INSERT INTO merchants VALUES ('2', 'abc.com', '127.0.0.1');
INSERT INTO merchants VALUES ('3', 'abc.com', '112.112.112.112');
INSERT INTO merchants VALUES ('4', 'bbc.com', '127.0.0.1');
INSERT INTO merchants VALUES ('5', 'bbc.com', '127.0.0.1');

这是mysql语句,我想实现
abc.com 有效ip为2
bbc.com 有效ip为1
这语句怎么写,求大神指点指点！谢谢！

Answer 1

SELECT `name`, COUNT(DISTINCT ip) AS '有效IP' FROM merchants  GROUP BY `name`