英文:
Get data monthly wise in Sql query from single Column
问题
我需要从表格中检索“按月销售的数量”。
如何从同一列中提取所有按月划分的数据。
SELECT IH_ITEMNO，SUM(IH_QTY)作为七月
from tbl_item_history
where IH_DATE LIKE '2022-07%'
和IH_ITEMNO像'%PG%'和IH_TYPE ='S'
按IH_ITEMNO升序排序
此查询仅返回“七月”数据，因为输入参数为“七月”。如何在“where”子句中不传递输入参数的情况下获取所有“月份数据”。
注意：-“IH_DATE”包含所有月份的值。
输出将如下所示：“ITEMNO，七月，八月，九月，十月......”
英文:
I need to retrieve monthly wise quantity sold from a table.
How can i fetch all monthly wise data from the same column.
SELECT IH_ITEMNO, SUM(IH_QTY) as July
from tbl_item_history
where IH_DATE LIKE '2022-07%'
and IH_ITEMNO like '%PG' and IH_TYPE ='S'
group by IH_ITEMNO
order By IH_ITEMNO asc
This query return only July data as the input parameter is july. How can I get all the months data without passing input parameter in where clause.
Note: - IH_DATE holds all the values of months.
The output would like be ITEMNO, July, Aug, Sep, Oct......
答案1
得分: 0
我找不到一个好的示例，所以尝试这个（您需要添加额外的列）;
SELECT IH_ITEMNO,
SUM(IIF(IH_DATE LIKE '____-06%',IH_QTY,0)) as 六月,
SUM(IIF(IH_DATE LIKE '____-07%',IH_QTY,0)) as 七月,
SUM(IIF(IH_DATE LIKE '____-08%',IH_QTY,0)) as 八月,
SUM(IIF(IH_DATE LIKE '____-09%',IH_QTY,0)) as 九月
from tbl_item_history
where IH_DATE LIKE '2022%'
and IH_ITEMNO like '%PG' and IH_TYPE ='S'
group by IH_ITEMNO
order By IH_ITEMNO asc
此外，要注意存储日期的方式。IH_DATE 是什么数据类型？
编辑：
考虑到 IH_DATE 是日期数据类型，最好避免隐式转换（出于性能和防御性编程的原因）
下面的代码不进行任何隐式转换，对于具有 IH_DATE 索引的大型表可能会更快：
SELECT IH_ITEMNO,
SUM(IIF(IH_DATE >= '2022-06-01' AND IH_DATE < '2022-07-01',IH_QTY,0)) as 六月,
SUM(IIF(IH_DATE >= '2022-07-01' AND IH_DATE < '2022-08-01',IH_QTY,0)) as 七月
from tbl_item_history
where IH_DATE >= '2022-01-01' AND IH_DATE < '2023-01-01'
and IH_ITEMNO like '%PG' and IH_TYPE ='S'
group by IH_ITEMNO
order By IH_ITEMNO asc
编辑 2：
还要注意，有许多方法可以进行数据透视。在您的情况下，所需的输出列是已知和固定的，因此这使得问题变得更简单。
还有一些新的数据透视语法这里，您可能想使用它，但我认为它只是一种语法糖。
英文:
I couldn't find a good example, so try this (you'll need to add the additional columns);
SELECT IH_ITEMNO,
SUM(IIF(IH_DATE LIKE '____-06%',IH_QTY,0)) as June,
SUM(IIF(IH_DATE LIKE '____-07%',IH_QTY,0)) as July,
SUM(IIF(IH_DATE LIKE '____-08%',IH_QTY,0)) as August,
SUM(IIF(IH_DATE LIKE '____-09%',IH_QTY,0)) as September
from tbl_item_history
where IH_DATE LIKE '2022%'
and IH_ITEMNO like '%PG' and IH_TYPE ='S'
group by IH_ITEMNO
order By IH_ITEMNO asc
Also be careful of storing dates. What data type is IH_DATE?
<h3>Edit</h3>
Given that IH_DATE is data type date, it's best to avoid implicit casts (for reasons of performance and defensive programming)
The code below doesn't do any implicit casts and will probably be faster for a large table with an index on IH_DATE
SELECT IH_ITEMNO,
SUM(IIF(IH_DATE >= '2022-06-01' AND IH_DATE < '2022-07-01',IH_QTY,0)) as June,
SUM(IIF(IH_DATE >= '2022-07-01' AND IH_DATE < '2022-08-01',IH_QTY,0)) as July
from tbl_item_history
where IH_DATE >= '2022-01-01' AND IH_DATE < '2023-01-01'
and IH_ITEMNO like '%PG' and IH_TYPE ='S'
group by IH_ITEMNO
order By IH_ITEMNO asc
<h3>EDIT 2</h3>
Also be aware that there are many ways to do pivots. In your case the required ourput columns are known and fixed, so this makes it simpler.
There is also some newer pivot syntax here that you might want to use instead but I believe it's just syntactical sugar.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论