我需要帮助找出如何仅显示每个分店中最常见的产品。这适用于数据库中的每个分店。
附注:这是在单个表格中完成的(不需要其他表格来实现),只需要两个属性。
附注:这是在单个表格中完成的(不需要其他表格来实现),只需要两个属性。
基于Sean的数据库样例,我创建了查询,这可以完成任务,但请注意您在应用程序中的方式看起来资源昂贵。尝试思考另一种发现最常见产品的方法,而不是数据库查询。
SELECT
a.branch_id, `branch_product` , COUNT(branch_product) AS cnt , b.mx
FROM
branch_products a
LEFT JOIN
(SELECT
branch_id, MAX(cnt) AS mx
FROM
(SELECT branch_id, COUNT(branch_product) AS cnt FROM branch_products GROUP BY branch_id, branch_product ) AS maxes
GROUP BY branch_id) b
ON a.branch_id = b.branch_id
GROUP BY branch_id, branch_product
HAVING cnt=mx
SELECT * FROM (
SELECT *, count(branch_product) AS cou FROM branch_products
GROUP BY branch_id, branch_product
ORDER BY cou DESC
) AS ordbycou
GROUP BY branch_id
生成:
+-----------+----------------+-----+
| branch_id | branch_product | cou |
+-----------+----------------+-----+
| 1 | trainers | 3 |
| 2 | suit | 2 |
| 3 | suit | 1 |
+-----------+----------------+-----+
3 rows in set (0.00 sec)
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE IF NOT EXISTS `branch_products` (
`branch_id` mediumint(8) unsigned NOT NULL,
`branch_product` varchar(128) NOT NULL,
KEY `branch_id` (`branch_id`),
KEY `branch_product` (`branch_product`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `branch_products` (`branch_id`, `branch_product`) VALUES
(1, 'trainers'),
(1, 'trainers'),
(1, 'trainers'),
(1, 'tie'),
(1, 'tie'),
(1, 'suit'),
(2, 'suit'),
(2, 'suit'),
(2, 'tie'),
(3, 'suit');
(1, 'trainers'), (1, 'tie'),
结果应该会产生它们两个,因为它们出现的次数相同。 - Martynogea