各位sql大拿，帮忙统计一个不同日期二次消费的人数。

[Hx_Moon_]

各位sql大神帮忙解下，业务需求-》 统计不同地区不同日期两次以上消费的人数，同一天消费两次或两次以上的只能算一次，结果如下表
area_name 二次消费人数
深圳 1
北京 2
上海 0

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0',
  `area_id` int(11) NOT NULL DEFAULT '0',
  `dt` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('1', '1', '100', '2017-03-15');
INSERT INTO `order` VALUES ('1', '1', '100', '2017-03-15');
INSERT INTO `order` VALUES ('1', '1', '100', '2017-03-16');
INSERT INTO `order` VALUES ('1', '1', '101', '2017-03-16');
INSERT INTO `order` VALUES ('1', '2', '101', '2017-03-16');
INSERT INTO `order` VALUES ('1', '2', '101', '2017-03-17');
INSERT INTO `order` VALUES ('1', '3', '101', '2017-03-17');
INSERT INTO `order` VALUES ('1', '3', '101', '2017-03-18');
INSERT INTO `order` VALUES ('1', '3', '101', '2017-03-19');
INSERT INTO `order` VALUES ('1', '3', '101', '2017-03-20');
INSERT INTO `order` VALUES ('1', '4', '102', '2017-03-20');

CREATE TABLE `area` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `area_name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of area
-- ----------------------------
INSERT INTO `area` VALUES ('100', '深圳');
INSERT INTO `area` VALUES ('101', '北京');
INSERT INTO `area` VALUES ('102', '上海');

[二月十六]

select area_name,
(select COUNT(1) from (SELECT  area_name ,
        COUNT(DISTINCT dt) AS num
FROM    `order` JOIN area ON area_id = area.id
GROUP BY area_name,`order`.uid)t 
where t.area_name = area.area_name AND num>=2) as 二次消费人数 
FROM area 

[Hx_Moon_]

引用 1 楼 sinat_28984567 的回复:

select area_name,
(select COUNT(1) from (SELECT  area_name ,
        COUNT(DISTINCT dt) AS num
FROM    `order` JOIN area ON area_id = area.id
GROUP BY area_name,`order`.uid)t 
where t.area_name = area.area_name AND num>=2) as 二次消费人数 
FROM area 

厉害了word哥