每月销售量列-Java 学习之路

SQL问题，将月度和年度销售列为列：

我有两张 table ：

t_items（item_num，item_name，itm_qnt）
t_items_dtl（item_num，invdate，itm_qnt_out，itm_qnt_in）

专栏是：

itm_qnt =当前库存
invdate =发票日期
item_qnt_out =发票中已售出的特定商品的数量
item_qnt_in =发票中返回的特定项目的编号

我想获得以下列：

item_num | item_name | currnt_stock | QTY sold 2018 | QTY sold:Jan2018 | Feb 2018 | March 2018 .... Dec 2018

现在我使用了这个查询，但它只给出了每个项目的总销售额：

SELECT 
    i.item_num AS ItemNum, 
    i.item_name AS ItemName, 
    sum(d.itm_qnt_out-d.itm_qnt_in) AS QTOUT,
    i.itm_qnt AS Stock
FROM 
    dbo.t_items AS i
FULL OUTER JOIN  
    t_items_dtl AS d ON i.item_num = d.item_num
GROUP BY 
    i.item_num, i.item_name, i.itm_qnt;

如何按列（而不是行）添加月度和年度销售额？

1 回答

根据您使用的数据库，可能有更优雅的方式来从日期获得年份和月份，但可能需要以下内容：

SELECT 
    i.item_num AS ItemNum, 
    i.item_name AS ItemName, 
    sum(d.itm_qnt_out-d.itm_qnt_in) AS QTOUT,
    sum(CASE WHEN to_char(invdate,'mm') = '01' THEN d.itm_qnt_out-d.itm_qnt_in ELSE 0 END) AS QTOUTJan,
    sum(CASE WHEN to_char(invdate,'mm') = '02' THEN d.itm_qnt_out-d.itm_qnt_in ELSE 0 END) AS QTOUTFeb,
    sum(CASE WHEN to_char(invdate,'mm') = '03' THEN d.itm_qnt_out-d.itm_qnt_in ELSE 0 END) AS QTOUTMar,
/*Repeat for every month*/
    i.itm_qnt AS Stock
FROM 
    dbo.t_items AS i
FULL OUTER JOIN  
    t_items_dtl AS d ON i.item_num = d.item_num
WHERE to_char(invdate, 'yyyy') = '2018'

GROUP BY 
    i.item_num, i.item_name, i.itm_qnt;

与您的代码相比，我已经为每个月定义了一个列，添加了一个 WHERE 来过滤整个事物到2018年 .

回复于 2024-05-18T13:29:08+08:00

每月销售量列

1 回答

相关问题