显示出现次数最多的组
显示出现次数最多的组
提问于 2017-11-08 13:57:24
t_table看起来像：
+-----------+---------+--------------+------------------+-----------------------+----------------------------------+
| pk_IdLoan | fk_IdCar| fk_IdCustomer| fk_Source_Agency | fk_Destination_Agency | RentalDate | DeliveryDate | Cost |
+-----------+---------+--------------+------------------+-----------------------+----------------------------------+我写了一个查询：
(SELECT fk_IdCustomer, MONTHNAME(RentalDate) AS Month, YEAR(RentalDate) As Year, COUNT(*)
FROM t_loan
GROUP BY fk_IdCustomer, Month, Year);这会导致
+---------------+-------------+------+----------+
| fk_IdCustomer | Month | Year | COUNT(*) |
+---------------+-------------+------+----------+
| 1 | July | 2016 | 3 |
| 1 | November | 2017 | 1 |
| 1 | September | 2016 | 7 |
| 5 | May | 2016 | 1 |
| 6 | January | 2016 | 1 |
| 6 | September | 2017 | 2 |
+---------------+-------------+------+----------+现在我想得到每一个客户的这几个月和几年，从而得到最高的COUNT(*)，f.e.：
+---------------+-------------+------+----------+
| fk_IdCustomer | Month | Year | COUNT(*) |
+---------------+-------------+------+----------+
| 1 | September | 2016 | 7 |
| 5 | May | 2016 | 1 |
| 6 | September | 2017 | 2 |
+---------------+-------------+------+----------+如何做到这一点？
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-11-08 14:09:16
这在MySQL中有点痛苦，因为它不支持CTE或窗口函数。一种方法是：
SELECT fk_IdCustomer, MONTHNAME(RentalDate) AS Month,
YEAR(RentalDate) As Year, COUNT(*) as cnt
FROM t_loan l
GROUP BY fk_IdCustomer, Month, Year
HAVING cnt = (SELECT COUNT(*)
FROM t_loan l2
WHERE l2.fk_IdCustomer = l.fk_IdCustomer
GROUP BY MONTHNAME(RentalDate), YEAR(RentalDate)
ORDER BY COUNT(*) DESC
LIMIT 1
);注意:如果有重复，您将得到所有匹配的值。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47181593
复制相关文章
相似问题
腾讯云开发者
