mysql - 我的带有限制条件的 mysql 查询错误

Question

我有3张 table 客户、时间和销售额我想找出所有客户的年收入条件是没有 child 且收入必须大于我们设置的限制的客户我的表结构客户

CREATE TABLE `customers` (
  `customer_id` int(11) DEFAULT NULL,
  `account_num` double DEFAULT NULL,
  `lname` varchar(50) DEFAULT NULL,
  `fname` varchar(50) DEFAULT NULL,
  `mi` varchar(50) DEFAULT NULL,
  `address1` varchar(50) DEFAULT NULL,
  `address2` varchar(50) DEFAULT NULL,
  `address3` varchar(50) DEFAULT NULL,
  `address4` varchar(50) DEFAULT NULL,
  `postal_code` varchar(50) DEFAULT NULL,
  `region_id` int(11) DEFAULT NULL,
  `phone1` varchar(50) DEFAULT NULL,
  `phone2` varchar(50) DEFAULT NULL,
  `birthdate` datetime DEFAULT NULL,
  `marital_status` varchar(50) DEFAULT NULL,
  `yearly_income` varchar(50) DEFAULT NULL,
  `gender` varchar(50) DEFAULT NULL,
  `total_children` smallint(6) DEFAULT NULL,
  `num_children_at_home` smallint(6) DEFAULT NULL,
  `education` varchar(50) DEFAULT NULL,
  `member_card` varchar(50) DEFAULT NULL,
  `occupation` varchar(50) DEFAULT NULL,
  `houseowner` varchar(50) DEFAULT NULL,
  `num_cars_owned` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

销售

CREATE TABLE `sales` (
  `product_id` int(11) DEFAULT NULL,
  `time_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `store_sales` float DEFAULT NULL,
  `store_cost` float DEFAULT NULL,
  `unit_sales` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

次

CREATE TABLE `times` (
  `time_id` int(11) DEFAULT NULL,
  `the_date` datetime DEFAULT NULL,
  `the_day` varchar(50) DEFAULT NULL,
  `the_month` varchar(50) DEFAULT NULL,
  `the_year` smallint(6) DEFAULT NULL,
  `day_of_month` smallint(6) DEFAULT NULL,
  `month_of_year` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我的问题是:- 找到没有 child 且 yearly_income 大于用户在运行查询时给出的限制的客户列表。

我的查询是

SET @limit=50;
SELECT customers.`fname`, customers.`lname` ,ROUND(SUM(sales.store_sales)) as income,times.the_year
 FROM `sales` 
 LEFT JOIN times
 ON sales.time_id=times.time_id
 LEFT JOIN customers 
ON customers.customer_id=sales.customer_id 
WHERE income>@limit AND `total_children`=0
GROUP BY sales.customer_id,times.the_year 

我收到此错误:#1054 - “where 子句”中的未知列“收入”

Answer 1

您别名为 income 的数量是一个聚合，因此在 WHERE 子句中引用它没有意义。将此 WHERE 逻辑移至 HAVING 子句:

SET @limit=50;
SELECT
    c.fname,
    c.lname,
    ROUND(SUM(s.store_sales)) AS income,
    t.the_year
FROM sales s 
LEFT JOIN times t
    ON s.time_id = t.time_id
LEFT JOIN customers c
    ON c.customer_id = s.customer_id 
WHERE
    total_children = 0
GROUP BY
    c.customer_id,
    t.the_year 
HAVING
    ROUND(SUM(s.store_sales)) > @limit;

请注意，从技术上讲，我们可以在 HAVING 子句中使用别名:

HAVING income > @limit;

但这不能移植到大多数其他数据库。此外，我在查询中引入了别名，这样更易​​于阅读。