mysql - 一个复杂的MySQL查询

我试图编写一个复杂的sql查询，但无法获得具有正确值的结果集。
coupons桌上有一些在线优惠券。
merchants表保存商家信息，并与coupons绑定。
coupons.merchant_id表保存值为branches和lat的商家分支（用于计算距离和最近的分支等），并与lng表绑定。
merchants表格关联branches.merchant_id和coupons_branches表格。
coupons桌上有一些特殊的地方，如购物中心等，并与branches桌相连。
下面是我的桌子结构；

CREATE TABLE IF NOT EXISTS `branches` (
  `branch_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) unsigned NOT NULL DEFAULT '0',
  `place_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `branch` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `postcode` varchar(6) COLLATE utf8_unicode_ci DEFAULT '',
  `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lat` float(10,6) DEFAULT NULL,
  `lng` float(10,6) DEFAULT NULL,
  `status` tinyint(4) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`branch_id`),
  KEY `lat` (`lat`),
  KEY `lng` (`lng`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `coupons` (
  `coupon_id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `merchant_id` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `coupon` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `description` longtext COLLATE utf8_unicode_ci,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `coupon_usage` int(10) unsigned NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','passive','deleted','preview') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  PRIMARY KEY (`coupon_id`),
  KEY `start_date` (`start_date`),
  KEY `end_date` (`end_date`),
  KEY `merchant_id` (`merchant_id`),
  KEY `category_id` (`category_id`),
  KEY `status` (`status`),
  KEY `created` (`created`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `coupons_branches` (
  `branch_id` int(11) unsigned NOT NULL DEFAULT '0',
  `coupon_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`branch_id`,`coupon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `merchants` (
  `merchant_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `merchant` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `website` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','passive','deleted') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'passive',
  PRIMARY KEY (`merchant_id`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `places` (
  `place_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `place` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`place_id`),
  KEY `place` (`place`),
  KEY `city_id` (`city_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

这是我的SQL语句；

SELECT * FROM (SELECT coupons.coupon_id AS c_id, coupons.category_id, coupons.coupon, merchants.merchant_id, merchants.merchant, (((acos(sin((41.02287686 * pi() / 180)) * sin((branches.lat * pi() / 180)) + cos((41.02287686 * pi() / 180)) * cos((branches.lat * pi()/ 180)) * cos(((29.04632806 - branches.lng) * pi() / 180)))) * 180 / pi()) * 60 * 1.1515 * 1.609344) as distance, COUNT(coupons.coupon_id) AS total_coupons
FROM (`coupons`)
INNER JOIN `coupons_branches` ON `coupons`.`coupon_id` = `coupons_branches`.`coupon_id`
RIGHT OUTER JOIN `branches` ON `coupons_branches`.`branch_id` = `branches`.`branch_id`
LEFT OUTER JOIN `merchants` ON `coupons`.`merchant_id` = `merchants`.`merchant_id`
WHERE `coupons`.`status` =  'active'
AND `merchants`.`status` =  'active'
GROUP BY `merchants`.`merchant_id`
HAVING `distance` <= 25000
ORDER BY `merchants`.`merchant`) as T2 GROUP BY c_id ORDER BY merchant

我在查一个商人的全部优惠券。我只想列出那个商人的最新优惠券。我不想列出所有的优惠券。我想先把商家分组，显示商家有多少优惠券。然后在另一页，我会展示属于那个商人的优惠券。
除了places列之外，一切都很好。未正确计算“优惠券总数”列。如果我在下面写这样的话，它的计算是正确的。

SELECT * FROM (SELECT coupons.coupon_id, coupons.coupon_id AS c_id, coupons.category_id, coupons.coupon, merchants.merchant_id, merchants.merchant, coupons.coupon_usage, COUNT(coupons.coupon_id) AS total_coupons
FROM (`coupons`)
LEFT OUTER JOIN `merchants` ON `coupons`.`merchant_id` = `merchants`.`merchant_id`
WHERE `coupons`.`status` =  'active'
AND `merchants`.`status` =  'active'
GROUP BY `merchants`.`merchant_id`
ORDER BY `merchants`.`merchant`) as T2 GROUP BY c_id ORDER BY merchant

branches和branches.place_id表有问题，但我找不到。

最佳答案

尝试将COUNT()表达式更改为COUNT(DISTINCT coupons.coupon_id)。如果没有distinct关键字，即使是重复的优惠券也会被计入商家优惠券的总数。我猜你只是在寻找目前分发给分行的优惠券的总数。