SQl查询按月分组，结果有些空? [英] SQl query grouping by month with some empty result?

问题描述

您好，我正在尝试执行SQL查询以获取折线图的数据，在这里需要显示数据的趋势，这是我想出的SQL查询:

Hi, i am trying to do an SQL query for get a data for a line chart where i need to show the trends of the data, here is the sql query i came up with:

SELECT
    material
,   sum(CASE WHEN MONTH(date_in) = 1 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "JAN 2009"
,   sum(CASE WHEN MONTH(date_in) = 2 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "FEB 2009"
,   sum(CASE WHEN MONTH(date_in) = 3 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "MAR 2009"
,	sum(CASE WHEN MONTH(date_in) = 4 AND YEAR(date_in)=2009 THEN amount ELSE 0 END) AS "APR 2009"
FROM rec_stats
GROUP BY material
ORDER BY material

桌子是这样的:

the table is something like this:

|  material |  amount | date_in | user |

该用户现在不相关，

基本上，我需要得到这样的结果:

the user is irrelevant right now,

basically, i need to get the result to be like this:

material | jan 2009 | feb 2009 | .......... | dec 2010 | jan 2011 | ..........
1        |   120    |    0     | .......... |   200    |   0      | .........
2        |   0      |   300    | ..........................................
.
.
.

但是正如您所看到的，我需要一个一个地定义分组，当我不知道数据的时间范围是什么时候，这实际上是不可能的...

我将使用C#进行实现，并且我想知道是否仍然可以根据现有数据生成字符串?

but as you can see, i need to define the grouping one by one, and this isn''t really possible when i don''t know when is the time range of the data...

i will be using C# for the implementation, and i wonder if there is anyway to generate the string based on the existing data?

推荐答案

要求数据看起来如何精确，问题很简单.您需要将查询重写为类似以下内容的内容:

If you can ease up on the requirement of how exactly the data will look, the problem is quite simple. You''ll need to rewrite your query to be something like:

SELECT
  material,
  SUM([Amount]) AS [Amount],
  RIGHT(CONVERT(varchar(50), [date_in], 106), 8) AS [Date]
FROM
  [rec_stats]
GROUP BY
  material,
  YEAR([date_in]),
  MONTH([date_in])
ORDER BY
  material,
  [date_in]

您将获得以下格式的数据:

You''ll get data in the following format:

material | amount | date

从那里，您只需要将C#中的数据转换为可以使用的表单即可.除了上述内容，您还有其他选择:

-动态SQL(即编写SQL语句并执行它的SQL)
- SQL Pivot [

From there, you''ll just have to pivot the data in C# to a form you can use. Aside from the above, the other options you have would be:

-Dynamic SQL (i.e. SQL that writes SQL statements and executes it)
-SQL Pivot[^] - I don''t consider this much different than Dynamic SQL, since you have to define what each column is, but I just wanted to state it.

您可以尝试这样的方法吗:

Can you try something like this:

GROUP BY dateadd(month, datediff(month, 0, date_in), 0)

我相信，这将按月对统计数据进行分组.我无法测试这是否可行，我现在没有跨月数据.

That will group the stats by each month I believe. I can''t test if this works, I don''t have month spanning data right now.

这篇关于SQl查询按月分组，结果有些空?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！