SQL按月分组并加入

debugcn 发表于 Dev

用户名

我有3个不同的查询，需要找到一种在单个gridview中显示结果的方法，但是这种方法是创建3个不同的网格。我的代码是这样，尝试找到一种加入单行的方法，其中行显示月份，列显示“笔记本电脑”，“ PC”和“总计”。

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0) AS MyMonth, COUNT(stb_group) AS Laptop 
FROM inventary 
WHERE (activation_date > '10-01-2013') AND (stb_group = 'Laptop') 
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0)


SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0) AS MyMonth, COUNT(stb_group) AS PC 
FROM inventary 
WHERE (activation_date > '10-01-2013') AND (stb_group = 'PC') 
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0)


SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0) AS MyMonth, COUNT(stb_group) AS Units
FROM         inventary
WHERE     (activation_date > '10-01-2013')
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0)

rs。

试试这个

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0) AS MyMonth, 
COUNT(case when stb_group = 'Laptop' then stb_group end) AS Laptop,
COUNT(case when stb_group = 'PC' then stb_group end) AS PC, 
COUNT(stb_group) AS Units
FROM inventary 
WHERE (activation_date > '10-01-2013')  
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, activation_date), 0)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。