显示出现次数最多的组

debugcn 发表于 Dev

三木

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(*)fe：

+---------------+-------------+------+----------+
| fk_IdCustomer | Month       | Year | COUNT(*) |
+---------------+-------------+------+----------+
| 1             | September   | 2016 | 7        |
| 5             | May         | 2016 | 1        |
| 6             | September   | 2017 | 2        |
+---------------+-------------+------+----------+

如何实现这一目标？

戈登·利诺夫

这在 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
             );

注意：如果有重复，您将获得所有匹配的值。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。