MySQL查询租赁次数≥10的客户信息问题求助

需求 #

查询租赁次数至少为10次的客户的ClientCode、ClientNom及租赁次数。

之前的尝试与问题 #

1. 初始查询

Select client.ClientCode, client.ClientNom, (location.LocationRef)
FROM client
  INNER JOIN location ON client.ClientCode = location.ClientCode
WHERE location.LocationRef > 9
GROUP BY location.ClientCode;

问题：仅返回LocationRef大于9的第一条记录，未统计客户的总租赁次数。

2. 使用COUNT的查询

SELECT client.ClientCode, client.ClientNom, Count(location.LocationRef) 
  FROM client 
    INNER JOIN location 
      ON client.ClientCode = location.ClientCode WHERE location.LocationRef > 9 
GROUP BY location.ClientCode;

问题：仅统计了LocationRef大于9的租赁记录数（结果为5），但目标客户实际有13次租赁，不符合需求。

正确的MySQL查询语句 #

SELECT 
    client.ClientCode, 
    client.ClientNom, 
    COUNT(location.LocationRef) AS 租赁次数
FROM client
INNER JOIN location ON client.ClientCode = location.ClientCode
GROUP BY client.ClientCode, client.ClientNom
HAVING COUNT(location.LocationRef) >= 10;

查询说明 #

• 通过INNER JOIN关联client和location表，关联条件为客户编码ClientCode。
• 用COUNT(location.LocationRef)统计每个客户的总租赁次数，通过AS设置别名增强可读性。
• GROUP BY子句包含所有非聚合字段（client.ClientCode和client.ClientNom），符合MySQL分组规范。
• 使用HAVING子句过滤分组后租赁次数≥10的客户，避免了WHERE提前过滤记录导致的统计偏差。

数据库建表及插入脚本 #

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

CREATE TABLE `client` (
  `ClientCode` int(11) NOT NULL,
  `ClientNom` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `client` (`ClientCode`, `ClientNom`) VALUES
(123, 'Gaetan'),
(12874, 'Alex'),
(12875, 'Max');

CREATE TABLE `location` (
  `LocationRef` int(11) NOT NULL,
  `Immatriculation` varchar(11) NOT NULL,
  `ClientCode` int(11) NOT NULL,
  `MontantLocation` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `location` (`LocationRef`, `Immatriculation`, `ClientCode`, `MontantLocation`) VALUES
(1, 'AA-229-AA', 12874, 123), (2, 'AA-229-AB', 12875, 156),
(3, 'BA-229-AA', 12874, 700), (4, 'AB-229-AA', 12874, 678),
(5, 'AA-229-AB', 12874, 987), (6, 'AA-229-AB', 12874, 980),
(7, 'AB-229-AA', 12874, 567), (8, 'AA-229-AA', 12874, 7789),
(9, 'AA-229-AB', 12874, 567), (10, 'AA-229-AB', 12874, 456),
(11, 'AA-229-AA', 12874, 566), (12, 'AB-229-AA', 12874, 700),
(13, 'AA-229-AA', 12874, 899), (14, 'AA-229-AB', 12874, 67);

内容的提问来源于stack exchange，提问作者Narlex