SELECT count( b.category ) FROM ods_bill_food AS a LEFT JOIN manual_food_category AS b ON a.foodName = b.foodName WHERE ( b.category = '主食' OR b.category = '烧烤' OR b.category = '小菜' OR b.category = '小吃' OR b.category = '饮品' OR b.category = '卤味' ) AND a.reportdate BETWEEN '2023-04-01' AND '2023-04-02' GROUP BY b.category HAVING count( b.category ) = 1; 得出来的结果没有数据,哪里错了
时间: 2024-04-04 09:35:05 浏览: 69
根据你提供的 SQL 语句,查询结果没有数据的原因可能是没有满足 `HAVING COUNT(b.category) = 1` 的条件。这个条件表示只有在分类数量为1的情况下才会被统计。
出现这种情况的原因可能是查询结果中的某些分类数量不为1,因此在 `GROUP BY` 分组后,这些分类就被过滤掉了。
以下是一些可能的解决方案:
- 检查 `manual_food_category` 表中的分类是否正确,确保所有分类都只被分配给了一个 `foodName`。
- 检查 `ods_bill_food` 表中的数据是否正确,确保所有食品名称都被正确匹配到了分类。
- 尝试去掉 `HAVING COUNT(b.category) = 1` 条件,看看是否能够查询到数据。
希望能够帮助到你。
相关问题
SELECT count( b.category ) FROM ods_bill_foo d AS a LEFT JOIN manual_food_category AS b ON a.foodName = b.foodName WHERE ( b.category = '主食' OR b.category = '烧烤' OR b.category = '小菜' OR b.category = '小吃' OR b.category = '饮品' OR b.category = '卤味' ) AND a.reportdate BETWEEN '2023-04-01' AND '2023-04-02' HAVING count( b.category ) = 1 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS a LEFT JOIN manual_food_category AS b ON a.foodName = b.foodName WHERE ( ' at line 4 时间: 0.028s
根据你提供的 SQL 语句,我发现有一个小问题。在查询语句中,表别名 `d` 并没有在查询中被使用到,因此可以省略此表别名,例如:
```
SELECT COUNT(b.category)
FROM ods_bill_foo AS a
LEFT JOIN manual_food_category AS b ON a.foodName = b.foodName
WHERE (b.category = '主食' OR b.category = '烧烤' OR b.category = '小菜' OR b.category = '小吃' OR b.category = '饮品' OR b.category = '卤味')
AND a.reportdate BETWEEN '2023-04-01' AND '2023-04-02'
HAVING COUNT(b.category) = 1
```
希望能够解决问题。
SELECT count(b.category) FROM ods_bill_foo d AS a LEFT JOIN manual_food_category AS b ON a.foodName = b.foodName WHERE b.category = ( '主食' AND '烧烤' AND '小菜' AND '小吃' AND '饮品' AND '卤味' ) AND a.reportdate BETWEEN '2023-04-01' AND '2023-04-02' HAVING count( b.category ) = 1 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS a LEFT JOIN manual_food_category AS b ON a.foodName = b.foodName WHERE b.c' at line 4 时间: 0.017s
根据你提供的 SQL 语句,我发现有一个语法错误。在 where 子句中,不能使用多个 AND 来判断一个字段是否等于多个值。正确的写法应该是使用 IN 或者多个 OR 来进行判断,例如:
```
SELECT COUNT(b.category)
FROM ods_bill_foo AS a
LEFT JOIN manual_food_category AS b ON a.foodName = b.foodName
WHERE b.category IN ('主食', '烧烤', '小菜', '小吃', '饮品', '卤味')
AND a.reportdate BETWEEN '2023-04-01' AND '2023-04-02'
HAVING COUNT(b.category) = 1
```
或者
```
SELECT COUNT(b.category)
FROM ods_bill_foo AS a
LEFT JOIN manual_food_category AS b ON a.foodName = b.foodName
WHERE (b.category = '主食' OR b.category = '烧烤' OR b.category = '小菜' OR b.category = '小吃' OR b.category = '饮品' OR b.category = '卤味')
AND a.reportdate BETWEEN '2023-04-01' AND '2023-04-02'
HAVING COUNT(b.category) = 1
```
希望能够帮助到你。
阅读全文