时间戳

首页 > 解决方案 > MYSQL - JOIN TABLE 多次获取基于多个条件的单个值

mysql - MYSQL - JOIN TABLE 多次获取基于多个条件的单个值

问题描述

我正在尝试从ssku数据 访问记录在此处输入图像描述

和预测 在此处输入图像描述

我需要一个查询来获得低于输出 在此处输入图像描述

要求 从 SSKU 表中获取所有数据并加入预测表6次（sep,oct,nov,dec,jan,feb）以获取该特定 'month_year' 的 'qty' 和 salesID max 'Version' （最新版本）

对不起，我很不擅长解释。我可以根据您的疑问即兴提出这个问题。

我非常不擅长mysql。我只知道简单的 SQL。复杂的嵌套未知请帮助。

表 结构如下：

`**ahlaan_ssku`** 
CREATE TABLE `ahlaan_ssku` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `PlantID` int(11) DEFAULT NULL,
 `FormatID` int(11) DEFAULT NULL,
 `SSKU` varchar(50) DEFAULT NULL,
 `Description` varchar(150) DEFAULT NULL,
 `MarketID` int(11) DEFAULT NULL,
 `ReplPolicy` varchar(50) DEFAULT NULL,
 `SalesManagerID` int(11) DEFAULT NULL,
 `ActiveStatus` int(11) DEFAULT NULL,
 `Created_by` int(11) DEFAULT NULL,
 `Created_on` datetime DEFAULT NULL,
 `Modified_by` int(11) DEFAULT NULL,
 `Modified_on` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=411 DEFAULT CHARSET=utf8


**`ahlaan_forecast_monthly_sales`** 
CREATE TABLE `ahlaan_forecast_monthly_sales` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `SSKUID` int(11) DEFAULT NULL,
 `Month_Year` date DEFAULT NULL,
 `Qty` int(11) DEFAULT NULL,
 `StatusID` int(11) DEFAULT NULL,
 `Version` int(11) DEFAULT NULL,
 `Created_by` int(11) DEFAULT NULL,
 `Created_on` datetime DEFAULT NULL,
 `Modified_by` int(11) DEFAULT NULL,
 `Modified_on` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

我试过的查询

SELECT 
ssku.`id` as SSKUID, 
ssku.`SSKU`, 
ssku.`Description`,
salesForecastSep.Qty as Sep_2021_qty, salesForecastSep.Version as Sep2021_Version,
salesForecastOct.Qty as Oct_2021_qty, salesForecastOct.Version as Oct2021_Version,
salesForecastNov.Qty as Nov_2021_qty, salesForecastNov.Version as Nov2021_Version,
salesForecastDec.Qty as Dec_2021_qty, salesForecastDec.Version as Dec2021_Version,
salesForecastJan.Qty as Jan_2021_qty, salesForecastJan.Version as Jan2021_Version,
salesForecastFeb.Qty as Feb_2021_qty, salesForecastFeb.Version as Feb2021_Version
FROM `ahlaan_ssku` ssku 
   LEFT JOIN ( 
    SELECT * FROM `ahlaan_forecast_monthly_sales` WHERE Month_Year = '2021-09-01'  AND id IN (
            SELECT max(id) FROM `ahlaan_forecast_monthly_sales` GROUP BY Month_Year 
        ) 
    ) AS salesForecastSep ON ssku.id = salesForecastSep.SSKUID AND salesForecastSep.Month_Year = '2021-09-01' 
   LEFT JOIN ( 
    SELECT * FROM `ahlaan_forecast_monthly_sales` WHERE Month_Year = '2021-10-01'  AND id IN (
            SELECT max(id) FROM `ahlaan_forecast_monthly_sales` GROUP BY Month_Year 
        ) 
   ) AS salesForecastOct ON ssku.id = salesForecastOct.SSKUID AND salesForecastOct.Month_Year = '2021-10-01' 
   LEFT JOIN ( 
    SELECT * FROM `ahlaan_forecast_monthly_sales` WHERE Month_Year = '2021-11-01'  AND id IN (
            SELECT max(id) FROM `ahlaan_forecast_monthly_sales` GROUP BY Month_Year 
        ) 
   ) AS salesForecastNov ON ssku.id = salesForecastNov.SSKUID AND salesForecastNov.Month_Year = '2021-11-01' 
   LEFT JOIN ( 
    SELECT * FROM `ahlaan_forecast_monthly_sales` WHERE Month_Year = '2021-12-01'  AND id IN (
            SELECT max(id) FROM `ahlaan_forecast_monthly_sales` GROUP BY Month_Year 
        ) 
   ) AS salesForecastDec ON ssku.id = salesForecastDec.SSKUID AND salesForecastDec.Month_Year = '2021-12-01' 
   LEFT JOIN ( 
    SELECT * FROM `ahlaan_forecast_monthly_sales` WHERE Month_Year = '2022-01-01'  AND id IN (
            SELECT max(id) FROM `ahlaan_forecast_monthly_sales` GROUP BY Month_Year 
        ) 
   ) AS salesForecastJan ON ssku.id = salesForecastJan.SSKUID AND salesForecastJan.Month_Year = '2022-01-01' 
   LEFT JOIN ( 
    SELECT * FROM `ahlaan_forecast_monthly_sales` WHERE Month_Year = '2022-02-01'  AND id IN (
            SELECT max(id) FROM `ahlaan_forecast_monthly_sales` GROUP BY Month_Year 
        ) 
    ) AS salesForecastFeb ON ssku.id = salesForecastFeb.SSKUID AND salesForecastFeb.Month_Year = '2022-02-01'  WHERE ssku.ActiveStatus = '1' AND `SalesManagerID` = 302

它为我提供了 6 个月的值，但仅对于一个 SSKU，其他 SSKU 值显示为空

标签： mysql

解决方案

我已经删除了所有左连接并在 select 中添加了下面的代码

  (SELECT Qty FROM `ahlaan_forecast_monthly_sales` WHERE SSKUID = ssku.`id` AND Month_Year = '2021-10-01' ORDER BY Version DESC LIMIT 1) as Oct_2021, 
(SELECT Qty FROM `ahlaan_forecast_monthly_sales` WHERE SSKUID = ssku.`id` AND Month_Year = '2021-11-01' ORDER BY Version DESC LIMIT 1) as Nov_2021, 
(SELECT Qty FROM `ahlaan_forecast_monthly_sales` WHERE SSKUID = ssku.`id` AND Month_Year = '2021-12-01' ORDER BY Version DESC LIMIT 1) as Dec_2021, 
(SELECT Qty FROM `ahlaan_forecast_monthly_sales` WHERE SSKUID = ssku.`id` AND Month_Year = '2022-01-01' ORDER BY Version DESC LIMIT 1) as Jan_2022, 
(SELECT Qty FROM `ahlaan_forecast_monthly_sales` WHERE SSKUID = ssku.`id` AND Month_Year = '2022-02-01' ORDER BY Version DESC LIMIT 1) as Feb_2022, 
(SELECT Qty FROM `ahlaan_forecast_monthly_sales` WHERE SSKUID = ssku.`id` AND Month_Year = '2022-03-01' ORDER BY Version DESC LIMIT 1) as Mar_2022

这给了我想要的输出

推荐阅读