时间戳

首页 > 解决方案 > 如何在有条件的 SQL 中选择平均计数

mysql - 如何在有条件的 SQL 中选择平均计数

问题描述

我有两张桌子：

-----------------
| Branch        |
-----------------
| BCODE | BNAME |
|1      | City1 |
|2      | City2 |
-----------------

-----------------------
| Customer            |
-----------------------
| CNO | CNAME | BCODE |
-----------------------
|1    | Cust1 | 1     |
|2    | Cust2 | 1     |
|3    | Cust3 | 2     |
|4    | Cust4 | 2     |
|5    | Cust5 | 2     |
|6    | Cust6 | 2     |
-----------------------

我想要什么：客户数量少于每个分支机构平均客户数量的分支机构列表

我试过的（最接近答案）：

SELECT bcode, bname, customers from (
    SELECT 
        branch.BCODE as bcode, 
        branch.BNAME as bname ,
        count(customer.BCODE)as customers, 
        (select 
            count(customer.BCODE)/count(distinct customer.BCODE) 
        from customer) as average 
    FROM 
        branch 
    INNER JOIN
        customer 
    ON 
        branch.BCODE=customer.BCODE
    GROUP BY 
        branch.BCODE,
        branch.BNAME
    ) 
WHERE 
    average > customers;

我得到了什么：

#1064 - 您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以在第 19 行的“WHERE average > accounts LIMIT 0, 25”附近使用正确的语法

标签： mysqlsql

解决方案

问题出现在子查询的末尾，因为您没有别名。

SELECT bcode, bname, customers from (
    SELECT 
        branch.BCODE as bcode, 
        branch.BNAME as bname ,
        count(customer.BCODE)as customers, 
        (select 
            count(customer.BCODE)/count(distinct customer.BCODE) 
        from customer) as average 
    FROM 
        branch 
    INNER JOIN
        customer 
    ON 
        branch.BCODE=customer.BCODE
    GROUP BY 
        branch.BCODE,
        branch.BNAME
    ) a  -- Alias here
WHERE 
    average > customers;

检查DB<>FIDDLE并注意第 18 行。

推荐阅读