时间戳

首页 > 解决方案 > 查询左连接取数据问题-Mysql

mysql - 查询左连接取数据问题-Mysql

问题描述

我用左连接加入两个表。当我用 Select 全选时，所有行都来了（query1）。但是当我使用 select 拉出一些行时，我只加载了一个数据，而不是图 1 中的所有数据都加载了。是什么原因？

查询1 查询2

加载所有数据查询1

查询1：

SELECT * 
FROM oc_review AS OYLAMA 
JOIN oc_product AS PRODUCT 
    ON (PRODUCT.product_id = OYLAMA.product_id)
INNER JOIN oc_product_to_category AS KATEGORI 
    ON (KATEGORI.product_id = PRODUCT.product_id) 
WHERE KATEGORI.category_id = 69

仅加载 1 个数据查询2

查询2：

SELECT
    AVG(ALL OYLAMA.rating) AS ORTALAMA,
    COUNT(OYLAMA.rating) AS TOPLAMOY 
FROM oc_review AS OYLAMA 
JOIN oc_product AS PRODUCT
    ON (PRODUCT.product_id = OYLAMA.product_id) 
INNER JOIN oc_product_to_category AS KATEGORI
    ON (KATEGORI.product_id = PRODUCT.product_id) 
WHERE KATEGORI.category_id = 69

标签： mysqlsql

解决方案

一旦你引入聚合函数，如AVG()和 ， MySQL 就会创建一个单独的组，所有记录都会放在其中COUNT()。另请参阅问题MySQL Aggregate Functions without GROUP BY 子句以获取更多信息。

您可能想要做的是明确指定一个GROUP BY子句并将您拥有的字段添加GROUP BY到SELECT例如：

SELECT 
   PRODUCT.product_id, 
   AVG(ALL OYLAMA.rating) AS ORTALAMA,
   COUNT(OYLAMA.rating) AS TOPLAMOY 
FROM oc_review AS OYLAMA 
JOIN oc_product AS PRODUCT 
   ON(PRODUCT.product_id = OYLAMA.product_id) 
INNER JOIN oc_product_to_category AS KATEGORI 
   ON (KATEGORI.product_id = PRODUCT.product_id) 
WHERE KATEGORI.category_id = 69
GROUP BY PRODUCT.product_id

推荐阅读