通过SQL查询，得到相关产品，按天、月和年的入库数量

Question

想在SQL中，查询相关产品，按天、月和年的入库量，请帮我看看下面的查询语句的问题，修改完善一下，谢谢

select proName,Fmodel,DayInAmount,max(MonthInAmount),max(YearInAmount) 
            from(
            select distinct t3.FName as proName,t3.Fmodel as Fmodel,sum(t1.FQty) as DayInAmount from 
            ICStockBill t2 

            join ICStockBillEntry t1 on t1.FInterID = t2.FInterID 
            join t_icitem t3 on t3.FItemID = t1.FItemID
            join ICTransactionType t5 on t2.FTranType=t5.FID
            join t_StockPlace t4 on t4.FSPID = t1.FDCSPID  

            where t2.FTranType = '2' and (t4.FSPID = '2' or t4.FSPID = '1')  and t2.FDate = '2024-12-27'

            union

            select distinct t3.FName as proName,t3.Fmodel as Fmodel,sum(t1.FQty) as MonthInAmount from 
            ICStockBill t2 

            join ICStockBillEntry t1 on t1.FInterID = t2.FInterID 
            join t_icitem t3 on t3.FItemID = t1.FItemID
            join ICTransactionType t5 on t2.FTranType=t5.FID
            join t_StockPlace t4 on t4.FSPID = t1.FDCSPID  

            where t2.FTranType = '2' and (t4.FSPID = '2' or t4.FSPID = '1')  
            and t2.FDate >= '2024-12-1' and t2.FDate <= '2024-12-27'

            union

            select distinct t3.FName as proName,t3.Fmodel as Fmodel,sum(t1.FQty) as YearInAmount from 
            ICStockBill t2 

            join ICStockBillEntry t1 on t1.FInterID = t2.FInterID 
            join t_icitem t3 on t3.FItemID = t1.FItemID
            join ICTransactionType t5 on t2.FTranType=t5.FID
            join t_StockPlace t4 on t4.FSPID = t1.FDCSPID  

            where t2.FTranType = '2' and (t4.FSPID = '2' or t4.FSPID = '1')  
            and t2.FDate >= '2024-1-1' and t2.FDate <= '2024-12-27'

            ) as subquery

            group by proName,FModel

Answer 1

下午好🌅🌅🌅
本答案参考通义千问

要解决这个问题，我们需要分别计算每天、每月和每年的入库数量，并将这些数据合并到一个结果集中。以下是修改后的SQL查询：

1. 按天计算入库数量
2. 按月计算入库数量
3. 按年计算入库数量

我们将使用子查询来分别计算每天、每月和每年的入库数量，然后使用 UNION ALL 将这些子查询的结果合并在一起。

SELECT proName, Fmodel, DayInAmount, NULL AS MonthInAmount, NULL AS YearInAmount
FROM (
    SELECT 
        t3.FName AS proName,
        t3.Fmodel AS Fmodel,
        SUM(t1.FQty) AS DayInAmount,
        CAST(t2.FDate AS DATE) AS Day
    FROM 
        ICStockBill t2
    JOIN 
        ICStockBillEntry t1 ON t1.FInterID = t2.FInterID 
    JOIN 
        t_icitem t3 ON t3.FItemID = t1.FItemID
    JOIN 
        ICTransactionType t5 ON t2.FTranType = t5.FID
    JOIN 
        t_StockPlace t4 ON t4.FSPID = t1.FDCSPID  
    WHERE 
        t2.FTranType = '2' AND (t4.FSPID = '2' OR t4.FSPID = '1') AND t2.FDate = '2024-12-27'
    GROUP BY 
        t3.FName, t3.Fmodel, t2.FDate
) AS DayData

UNION ALL

SELECT proName, Fmodel, NULL AS DayInAmount, MonthInAmount, NULL AS YearInAmount
FROM (
    SELECT 
        t3.FName AS proName,
        t3.Fmodel AS Fmodel,
        SUM(t1.FQty) AS MonthInAmount,
        DATE_FORMAT(t2.FDate, '%Y-%m') AS Month
    FROM 
        ICStockBill t2
    JOIN 
        ICStockBillEntry t1 ON t1.FInterID = t2.FInterID 
    JOIN 
        t_icitem t3 ON t3.FItemID = t1.FItemID
    JOIN 
        ICTransactionType t5 ON t2.FTranType = t5.FID
    JOIN 
        t_StockPlace t4 ON t4.FSPID = t1.FDCSPID  
    WHERE 
        t2.FTranType = '2' AND (t4.FSPID = '2' OR t4.FSPID = '1') AND t2.FDate BETWEEN '2024-12-01' AND '2024-12-27'
    GROUP BY 
        t3.FName, t3.Fmodel, DATE_FORMAT(t2.FDate, '%Y-%m')
) AS MonthData

UNION ALL

SELECT proName, Fmodel, NULL AS DayInAmount, NULL AS MonthInAmount, YearInAmount
FROM (
    SELECT 
        t3.FName AS proName,
        t3.Fmodel AS Fmodel,
        SUM(t1.FQty) AS YearInAmount,
        YEAR(t2.FDate) AS Year
    FROM 
        ICStockBill t2
    JOIN 
        ICStockBillEntry t1 ON t1.FInterID = t2.FInterID 
    JOIN 
        t_icitem t3 ON t3.FItemID = t1.FItemID
    JOIN 
        ICTransactionType t5 ON t2.FTranType = t5.FID
    JOIN 
        t_StockPlace t4 ON t4.FSPID = t1.FDCSPID  
    WHERE 
        t2.FTranType = '2' AND (t4.FSPID = '2' OR t4.FSPID = '1') AND t2.FDate BETWEEN '2024-01-01' AND '2024-12-27'
    GROUP BY 
        t3.FName, t3.Fmodel, YEAR(t2.FDate)
) AS YearData;

解释

1. 按天计算入库数量:

   • 使用 CAST(t2.FDate AS DATE) 来确保日期格式正确。
   • 使用 GROUP BY t3.FName, t3.Fmodel, t2.FDate 来按天分组。

2. 按月计算入库数量:

   • 使用 DATE_FORMAT(t2.FDate, '%Y-%m') 来提取年份和月份。
   • 使用 GROUP BY t3.FName, t3.Fmodel, DATE_FORMAT(t2.FDate, '%Y-%m') 来按月分组。

3. 按年计算入库数量:

   • 使用 YEAR(t2.FDate) 来提取年份。
   • 使用 GROUP BY t3.FName, t3.Fmodel, YEAR(t2.FDate) 来按年分组。

通过这种方式，我们可以分别计算每天、每月和每年的入库数量，并将这些结果合并到一个结果集中。