时间戳

首页 > 解决方案 > 从多个表中选择数据并按类别分组

mysql - 从多个表中选择数据并按类别分组

问题描述

我有三个表，我想加入并获取类别中的数据。

Category Table
-id
-name

Product table
-id
-category_id
-name

Loading table
-product_id
-qty
-Outdate

Stock table
-product_id
-qty
-Indate

所以我想选择所有产品并将所有产品与特定的 category_id 相加，我希望这个输出

Category name                 open_balance      total_stock_in_jana   total_loadings_janaout
 ------------------------------------------------------------------------------------------
categori 1                    500                 0                               500
category 2                    400                 0                                400

但是这个 sql 是用它们的类别明智地获取所有产品，所以我想在类别中更改它

我的 sql

 SELECT 
        products.id, 
        products.name, products.open_stock as open_balance,

        (select categories.name from categories where categories.id=products.category_id) as 
         category_name,
        (select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and 
          DATE(stocks.Indate) 
        <= CURDATE()-1) as total_stock_in_jana,

        (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and 
        DATE(loadings.Outdate) <= CURDATE()-1) as total_loadings_jana,

        (select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and 
         DATE(stocks.Indate) 
        = CURDATE()) as total_stock_in_today,

        (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and 
        DATE(loadings.Outdate) = CURDATE()) as total_loadings_today

        from products

先感谢您

标签： mysqlsubquery

解决方案

试试这个查询.. 这里的 category 表被带到外面并添加了一个 Join with prod 表以进行分组。

SELECT categories.id, categories.name,
SUM(products.open_stock) AS open_balance,
SUM( (SELECT IFNULL(SUM(stocks.qty),0) FROM stocks WHERE stocks.pid=products.id AND stocks.Indate <= CURDATE()-1) ) AS total_stock_in_jana,
SUM( (SELECT IFNULL(SUM(loadings.qty),0) FROM loadings WHERE loadings.pid=products.id AND loadings.Outdate <= CURDATE()-1) ) AS total_loadings_jana,
SUM( (SELECT IFNULL(SUM(stocks.qty),0) FROM stocks WHERE stocks.pid=products.id AND stocks.Indate = CURDATE()) ) AS total_stock_in_today,
SUM( (SELECT IFNULL(SUM(loadings.qty),0) FROM loadings WHERE loadings.pid=products.id AND loadings.Outdate = CURDATE()) ) AS total_loadings_today
FROM categories INNER JOIN products ON categories.id = products.category_id GROUP BY categories.id;

推荐阅读