mysql - MySQL 错误 #1111 - 组函数的使用无效
问题描述
是的,这是一个任务。所以任务是输出两列“名字”和“姓氏”的条件:
-A u (B ∩ -C ∩ -(A ∩ -( B u D)))
A:所有周一和周五没有购物的消费者(time_by_day.the_day)
B:所有购买“非消耗品”(product_class.product_family)的消费者
C:一次购买超过10件商品(sales_fact_1997.unit_sales)的所有消费者(sales_fact_1997.time_id)
D:来自加拿大的女性消费者(consumer.gender,consumer.country)
这是我到目前为止得到的
SELECT
c.fname,
c.lname
FROM
customer AS c
INNER JOIN sales_fact_1997 AS s ON c.customer_id = s.customer_id
INNER JOIN time_by_day AS t ON s.time_id = t.time_id
INNER JOIN product AS p ON s.product_id = p.product_id
INNER JOIN product_class AS pc ON p.product_class_id = pc.product_class_id
Where
NOT t.the_day in ('Monday', 'Friday') OR
(
pc.product_family = 'Non-Consumable' AND
NOT SUM(s.unit_sales) > 10 AND
NOT (
t.the_day in ('Monday', 'Friday') AND
NOT (
pc.product_family = 'Non-Consumable' OR
(c.country = 'Canada' AND c.gender = 'F')
)
)
)
GROUP BY concat(c.customer_id, s.time_id)
以错误告终
#1111 - Invalid use of group function
但我不知道代码的哪一部分是错误的。我很确定这可能是 WHERE 部分。但我不知道我做错了什么。
条件C是我真正挣扎的地方。我可以很好地查询 C
SELECT
t.time_id,
c.customer_id,
c.fullname,
round(SUM(s.unit_sales),0) as tot
FROM
customer as c
INNER JOIN sales_fact_1997 as s ON c.customer_id = s.customer_id
INNER JOIN time_by_day as t on s.time_id=t.time_id
GROUP BY concat(c.customer_id, s.time_id)
ORDER BY c.customer_id, t.time_id
但是尝试将其合并到主代码中对我来说很难。
在线阅读我认为我应该使用 HAVING 而不是 WHERE。
如果有人能指出我正确的方向,我将不胜感激。
这是我使用的数据库。
解决方案
C:一次购买超过10件商品(sales_fact_1997.unit_sales)的所有消费者(sales_fact_1997.time_id)
你应该使用COUNT
not SUM
。
SELECT time_id,
count(*)
FROM sales_fact_1997
GROUP BY time_id
HAVING COUNT(*)>=10 ;
count(*)
不需要,我让只是为了显示结果
如果有帮助,你可以试试:
SELECT c.lname,
c.fname
FROM customer c
INNER JOIN
(
SELECT time_id,customer_id,product_id
FROM sales_fact_1997
GROUP BY time_id,customer_id,product_id
HAVING COUNT(*)>=10
) as s on c.customer_id=s.customer_id
INNER JOIN
(
SELECT time_id,the_day
FROM time_by_day
WHERE the_day
NOT IN ('Monday','Friday')
) as t on s.time_id=t.time_id
INNER JOIN
(
SELECT product_family,product_id
FROM product_class
INNER JOIN product
on product_class.product_class_id=product.product_class_id
WHERE product_family='Non-Consumable'
) pc on s.product_id=pc.product_id
where c.country='Canada' and c.gender ='F' ;